private command _____UPDATING
end _____UPDATING

private command updateThisLibrary
   // CAREFUL.
   ANSWER "YOU ARE ABOUT TO UPDATE THIS LIB. BREAKPOINT THROUGH WITH EXTREME CAUTION."
   
   -- DEFINE URLS
   answer "MAKE SURE THE DEFINED URLS STILL LEAD TO THE DATA AND THAT THE FORMATTING HAS NOT CHANGED"
   breakpoint
   put "http://openflights.svn.sourceforge.net/viewvc/openflights/openflights/data/routes.dat" into tRoutesURL
   put "http://openflights.svn.sourceforge.net/viewvc/openflights/openflights/data/airlines.dat" into tAirlinesURL
   put "http://openflights.svn.sourceforge.net/viewvc/openflights/openflights/data/airports.dat" into tAirportsURL
   
   -- GRAB UPDATED DBs
   put url tRoutesURL into tRoutesDB
   put url tAirlinesURL into tAirlinesDB
   put url tAirportsURL into tAirportsDB
   
   -- CLEAN THE DATA
   put cleanOpenFlightsData(tRoutesDB) into tRoutesDB
   put cleanOpenFlightsData(tAirlinesDB) into tAirlinesDB
   put cleanOpenFlightsData(tAirportsDB) into tAirportsDB
   
   --UPDATE INTO THE CUSTOM PROPS
   set the uFlightRoutes of this stack to tRoutesDB
   set the uAirlineData of this stack to tAirlinesDB
   set the uAirportData of this stack to tAirportsDB
end updateThisLibrary

private function cleanOpenFlightsData pData
   replace ",\N," with ",," in pData
   replace "," with "|" in pData
   replace quote with empty in pData
   return pData
end cleanOpenFlightsData

private command ____THE_API
end ____THE_API

function theAirlineData pAirlineCode
   /*
   Airline ID 	Unique OpenFlights identifier for this airline.
   Name 	Name of the airline.
   Alias 	Alias of the airline. For example, All Nippon Airways is commonly known as "ANA".
   IATA 	2-letter IATA code, if available.
   ICAO 	3-letter ICAO code, if available.
   Callsign 	Airline callsign.
   Country 	Country or territory where airline is incorporated.
   Active 	"Y" if the airline is or has until recently been operational, "N" if it is defunct. This field is not reliable: in particular, major airlines that stopped flying long ago, but have not had their IATA code reassigned (eg. Ansett/AN), will incorrectly show as "Y".
   */
   
   -- validate 
   put the number of chars in pAirlineCode into tChars
   if not(tChars is 2) AND not(tChars is 3) then return "ERROR: the airline code supplied must be 2 or 3 chars"
   if tChars is 2 then put 4 into tItemNum
   if tChars is 3 then put 5 into tItemNum
   
   --
   put the uAirlineData of me into tData
   filter tData with "*|" & pAirlineCode & "|*"
   
   set the itemdel to "|"
   repeat for each line thisLine in tData
      if item tItemNum of thisLine is pAirLineCode then put thisLine into tRecord
   end repeat
   
   
   put "ID|AIRLINE NAME|ALIAS|IATA|ICAO|CALLSIGN|COUNTRY|ACTIVE" into tFlds 
   replace "|" with return in tFlds
   return recordToArray(tRecord,tFlds)
   
end theAirlineData

function theRoutes pFrom pTo
   // CURRENTLY ONLY RETURN ONE RESULT
   /*
   Airline 	2-letter (IATA) or 3-letter (ICAO) code of the airline.
   Airline ID 	Unique OpenFlights identifier for airline (see Airline).
   Source airport 	3-letter (IATA) or 4-letter (ICAO) code of the source airport.
   Source airport ID 	Unique OpenFlights identifier for source airport (see Airport)
   Destination airport 	3-letter (IATA) or 4-letter (ICAO) code of the destination airport.
   Destination airport ID 	Unique OpenFlights identifier for destination airport (see Airport)
   Codeshare 	"Y" if this flight is a codeshare (that is, not operated by Airline, but another carrier), empty otherwise.
   Stops 	Number of stops on this flight ("0" for direct)
   Equipment 	3-letter codes for plane type(s) generally used on this flight, separated by spaces
   The data is ISO 8859-1 (Latin-1) encoded. The special value \N is used for "NULL" to indicate that no value is available, and is understood automatically by MySQL if imported.
   */
   
   -- validate 
   --breakpoint
   put the uFlightRoutes of me into tData
   filter tData with ("*|" & pFrom & "|*|" & pTo & "|*")
   put "AIRLINE|AIRLINE ID|FROM AIRPORT|FROM AIRPORT ID|TO AIRPORT|TO AIRPORT ID|CODESHARE Y/N|STOPS|EQUIPMENT" into tFlds
   replace "|" with return in tFlds
   
   return recordsToArray(tData,tFlds)
end theRoutes

function theAirportData pAirportCode
   /*
   Airport ID 	Unique OpenFlights identifier for this airport.
   Name 	Name of airport. May or may not contain the City name.
   City 	Main city served by airport. May be spelled differently from Name.
   Country 	Country or territory where airport is located.
   IATA/FAA 	3-letter FAA code, for airports located in Country "United States of America".
   3-letter IATA code, for all other airports.
   Blank if not assigned.
   ICAO 	4-letter ICAO code.
   Blank if not assigned.
   Latitude 	Decimal degrees, usually to six significant digits. Negative is South, positive is North.
   Longitude 	Decimal degrees, usually to six significant digits. Negative is West, positive is East.
   Altitude 	In feet.
   Timezone 	Hours offset from UTC. Fractional hours are expressed as decimals, eg. India is 5.5.
   DST 	Daylight savings time. One of E (Europe), A (US/Canada), S (South America), O (Australia), Z (New Zealand), N (None) or U (Unknown). See also: Help: Time
   */
   
   -- validate 
   put the number of chars in pAirportcode into tChars
   if pAirportCode is a number then put TRUE into tIDSearch else put FALSE into tIDSearch
   
   put the uAirportData of me into tData
   
   if tIDSearch then filter tData with (pAirportCode & "|*") else filter tData with "*|" & pAirportCode & "|*"
   put line 1 of tData into tRecord
   put "ID|AIRPORT NAME|CITY|COUNTRY|IATA/FAA|ICAO|LATITUDE|LONGITUDE|ALTITUDE|TIMEZONE|DST" into tFlds
   replace "|" with return in tFlds
   
   return recordToArray(tRecord,tFlds)
end theAirportData

function theAirportsDistancesTo pLat pLong pNumberOfResults
   put the uAirportLatLongs of me into pList
   put distancesToLocations(pLat,pLong,pList) into tResults
   
   put the number of lines in tResults into tAvailLines
   if not(pNumberOfResutls is a number) \
         OR pNumberOfResults is empty \
         OR pNumberOfResults > tAvailLines \ 
         then put tAvailLines into pNumberOfResults   
   
   set the itemdel to "|"
   repeat with x = 1 to pNumberOfResults
      put theAirportData(item 1 of line x of tResults) into tResultsA[x]
      put item 2 of line x of tResults into tResultsA[x]["DISTANCE_KM"]
   end repeat
   
   return tResultsA
end theAirportsDistancesTo


private command ____UTILS
end ____UTILS



function stripLeadingUnderscores pString
   repeat until not(char 1 of pString is "_")
      delete char 1 of pString
   end repeat
   return pString
end stripLeadingUnderscores



function distancesToLocations pOriginLat pOriginLong pList
   set the itemdel to "|"
   put empty into tDistList
   repeat for each line thisLine in pList
      put item 2 of thisLine into pEndLat
      put item 3 of thisLine into pEndLong
      put item 1 of thisLine into pID
      
      if (pEndLat is a number) AND (pEndLong is a number) then 
         put theHaversineDist(pOriginLat,pOriginLong,pEndLat,pEndLong) into tDistX
         put pID & "|" & tDistX & lf after tDistList
      end if
   end repeat
   
   filter tDistList without empty
   sort lines of tDistList ascending numeric by item 2 of each 
   return tDistList
end distancesToLocations



function generateAirportLatLongs
   put the uAirportData of me into tData
   set the itemdel to "|"
   put empty into tNew
   repeat for each line thisLine in tData
      put (item 1 of thisLine) & "|" & (item 7 of thisLine)  & "|" & (item 8 of thisLine) & lf after tNew 
   end repeat
   
   set the uAirportLatLongs of me to tNew
   return "done"
end generateAirportLatLongs

function theHaversineDist pLat1, pLon1, pLat2, pLon2
   //http://www.movable-type.co.uk/scripts/latlong.html
   /*
   Haversine formula: 	
   a = sin²(delta_lat/2) + cos(lat1).cos(lat2).sin²(delta_lon/2)
   c = 2.atan2(sqrt(a), sqrt(1-a))
   distance between two sets of lat long (1 and 2) = Radius of earth * c
   */
   put 6371 into tRadiusEarthKm
   -- COMPUTE DELTA LAT/LONGS, LATS, AND CONVERT EVERYTHING TO RADIANS
   put toRadians(pLat2-pLat1) into tDeltaLat
   put toRadians(pLon2-pLon1) into tDeltaLon
   put toRadians(pLat1) into tLat1
   put toRadians(pLat2) into tLat2
   
   -- COMPUTE "A"
   put sinSq(tDeltaLat/2) + (sinSq(tDeltaLon/2)*cos(tLat1)*cos(tLat2)) into tA
   -- COMPUTE "C"
   put 2 * atan2(sqrt(tA), sqrt(1-tA)) into tC // ATAN2 takes two args
   -- COMPUTE distance
   put tC*tRadiusEarthKm into tDistanceKm
   return tDistanceKm
   
end theHaversineDist

private function cleanLatLong pLat pLong
   /// THIS FUNCTION CAN BE CLEANED UP AND MADE MORE EFFICIENT
   set the itemdel to comma
   put "-,1,2,3,4,5,6,7,8,9,0,.,N,S,E,W" into tValid
   
   repeat with x = 1 to the number of chars in pLat
      if not(char x of pLat is among the items of tValid) then put "" into char x of pLat
   end repeat
   
   repeat with x = 1 to the number of chars in pLong
      if not(char x of pLong is among the items of tValid) then put "" into char x of pLong
   end repeat
   
   if the last char of pLat is among the items of "S,N" then
      if the last char of pLat is "S" then put "TRUE" into tNegLat
      put empty into the last char of pLat
      if tNegLat is "TRUE" then put ((-1)*pLat) into pLat
   end if
   
   if the last char of pLong is among the items of "E,W" then
      if the last char of pLong is "W" then put "TRUE" into tNegLong
      put empty into the last char of pLong
      if tNegLong is "TRUE" then put ((-1)*pLong) into pLong
   end if
   
   return pLat & comma & pLong
end cleanLatLong

private function stripSpaces pString
   replace " " with "" in pString
   return pString
end stripSpaces


private function sinSq pArg
   put sin(pArg) into tX // COMPUTE SIN just once
   return tX*tX 
end sinSq

private function toRadians pDegrees
   return ((pDegrees*3.14159) / 180)
end toRadians

private function toDegrees pRadians
   return ((pRadians * 180) / 3.14159)
end toDegrees



private function recordToArray pRecord pTableStructure
   put pTableStructure into tFlds
   replace "|" with return in pRecord
   put pRecord into tRecord
   
   -- DEPENDS ON BOTH THE FLDS AND tTable being in correct order.
   repeat the number of lines in tFlds
      put line 1 of tRecord into tRecordA[line 1 of tFlds]
      delete line 1 of tRecord
      delete line 1 of tFlds
   end repeat
   
   return tRecordA
end recordToArray

private function recordsToArray pRecords pTableStructure
   filter pRecords without empty
   put the number of lines in pRecords into tNumRecords
   repeat with x = 1 to tNumRecords
      put recordToArray(line x of pRecords, pTableStructure) into tResultA[x]
   end repeat
   return tResultA
end recordsToArray