#!/usr/bin/env python3
# (c) L.Spiegelberg 2020 fetch flight data from Bureau of Transportation Statistics

import requests
from tqdm import tqdm
import os

headers = {
    'Referer': 'https://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time',
    'Origin': 'https://www.transtats.bts.gov',
    'Content-Type': 'application/x-www-form-urlencoded',
}

params = (
    ('Table_ID', '236'),
    ('Has_Group', '3'),
    ('Is_Zipped', '0'),
)

data = 'UserTableName=Reporting_Carrier_On_Time_Performance_1987_present&DBShortName=&RawDataTable=T_ONTIME_REPORTING&sqlstr=+SELECT+YEAR%2CQUARTER%2CMONTH%2CDAY_OF_MONTH%2CDAY_OF_WEEK%2CFL_DATE%2COP_UNIQUE_CARRIER%2COP_CARRIER_AIRLINE_ID%2COP_CARRIER%2CTAIL_NUM%2COP_CARRIER_FL_NUM%2CORIGIN_AIRPORT_ID%2CORIGIN_AIRPORT_SEQ_ID%2CORIGIN_CITY_MARKET_ID%2CORIGIN%2CORIGIN_CITY_NAME%2CORIGIN_STATE_ABR%2CORIGIN_STATE_FIPS%2CORIGIN_STATE_NM%2CORIGIN_WAC%2CDEST_AIRPORT_ID%2CDEST_AIRPORT_SEQ_ID%2CDEST_CITY_MARKET_ID%2CDEST%2CDEST_CITY_NAME%2CDEST_STATE_ABR%2CDEST_STATE_FIPS%2CDEST_STATE_NM%2CDEST_WAC%2CCRS_DEP_TIME%2CDEP_TIME%2CDEP_DELAY%2CDEP_DELAY_NEW%2CDEP_DEL15%2CDEP_DELAY_GROUP%2CDEP_TIME_BLK%2CTAXI_OUT%2CWHEELS_OFF%2CWHEELS_ON%2CTAXI_IN%2CCRS_ARR_TIME%2CARR_TIME%2CARR_DELAY%2CARR_DELAY_NEW%2CARR_DEL15%2CARR_DELAY_GROUP%2CARR_TIME_BLK%2CCANCELLED%2CCANCELLATION_CODE%2CDIVERTED%2CCRS_ELAPSED_TIME%2CACTUAL_ELAPSED_TIME%2CAIR_TIME%2CFLIGHTS%2CDISTANCE%2CDISTANCE_GROUP%2CCARRIER_DELAY%2CWEATHER_DELAY%2CNAS_DELAY%2CSECURITY_DELAY%2CLATE_AIRCRAFT_DELAY%2CFIRST_DEP_TIME%2CTOTAL_ADD_GTIME%2CLONGEST_ADD_GTIME%2CDIV_AIRPORT_LANDINGS%2CDIV_REACHED_DEST%2CDIV_ACTUAL_ELAPSED_TIME%2CDIV_ARR_DELAY%2CDIV_DISTANCE%2CDIV1_AIRPORT%2CDIV1_AIRPORT_ID%2CDIV1_AIRPORT_SEQ_ID%2CDIV1_WHEELS_ON%2CDIV1_TOTAL_GTIME%2CDIV1_LONGEST_GTIME%2CDIV1_WHEELS_OFF%2CDIV1_TAIL_NUM%2CDIV2_AIRPORT%2CDIV2_AIRPORT_ID%2CDIV2_AIRPORT_SEQ_ID%2CDIV2_WHEELS_ON%2CDIV2_TOTAL_GTIME%2CDIV2_LONGEST_GTIME%2CDIV2_WHEELS_OFF%2CDIV2_TAIL_NUM%2CDIV3_AIRPORT%2CDIV3_AIRPORT_ID%2CDIV3_AIRPORT_SEQ_ID%2CDIV3_WHEELS_ON%2CDIV3_TOTAL_GTIME%2CDIV3_LONGEST_GTIME%2CDIV3_WHEELS_OFF%2CDIV3_TAIL_NUM%2CDIV4_AIRPORT%2CDIV4_AIRPORT_ID%2CDIV4_AIRPORT_SEQ_ID%2CDIV4_WHEELS_ON%2CDIV4_TOTAL_GTIME%2CDIV4_LONGEST_GTIME%2CDIV4_WHEELS_OFF%2CDIV4_TAIL_NUM%2CDIV5_AIRPORT%2CDIV5_AIRPORT_ID%2CDIV5_AIRPORT_SEQ_ID%2CDIV5_WHEELS_ON%2CDIV5_TOTAL_GTIME%2CDIV5_LONGEST_GTIME%2CDIV5_WHEELS_OFF%2CDIV5_TAIL_NUM+FROM++T_ONTIME_REPORTING+WHERE+Month+%3D1+AND+YEAR%3D2019&varlist=YEAR%2CQUARTER%2CMONTH%2CDAY_OF_MONTH%2CDAY_OF_WEEK%2CFL_DATE%2COP_UNIQUE_CARRIER%2COP_CARRIER_AIRLINE_ID%2COP_CARRIER%2CTAIL_NUM%2COP_CARRIER_FL_NUM%2CORIGIN_AIRPORT_ID%2CORIGIN_AIRPORT_SEQ_ID%2CORIGIN_CITY_MARKET_ID%2CORIGIN%2CORIGIN_CITY_NAME%2CORIGIN_STATE_ABR%2CORIGIN_STATE_FIPS%2CORIGIN_STATE_NM%2CORIGIN_WAC%2CDEST_AIRPORT_ID%2CDEST_AIRPORT_SEQ_ID%2CDEST_CITY_MARKET_ID%2CDEST%2CDEST_CITY_NAME%2CDEST_STATE_ABR%2CDEST_STATE_FIPS%2CDEST_STATE_NM%2CDEST_WAC%2CCRS_DEP_TIME%2CDEP_TIME%2CDEP_DELAY%2CDEP_DELAY_NEW%2CDEP_DEL15%2CDEP_DELAY_GROUP%2CDEP_TIME_BLK%2CTAXI_OUT%2CWHEELS_OFF%2CWHEELS_ON%2CTAXI_IN%2CCRS_ARR_TIME%2CARR_TIME%2CARR_DELAY%2CARR_DELAY_NEW%2CARR_DEL15%2CARR_DELAY_GROUP%2CARR_TIME_BLK%2CCANCELLED%2CCANCELLATION_CODE%2CDIVERTED%2CCRS_ELAPSED_TIME%2CACTUAL_ELAPSED_TIME%2CAIR_TIME%2CFLIGHTS%2CDISTANCE%2CDISTANCE_GROUP%2CCARRIER_DELAY%2CWEATHER_DELAY%2CNAS_DELAY%2CSECURITY_DELAY%2CLATE_AIRCRAFT_DELAY%2CFIRST_DEP_TIME%2CTOTAL_ADD_GTIME%2CLONGEST_ADD_GTIME%2CDIV_AIRPORT_LANDINGS%2CDIV_REACHED_DEST%2CDIV_ACTUAL_ELAPSED_TIME%2CDIV_ARR_DELAY%2CDIV_DISTANCE%2CDIV1_AIRPORT%2CDIV1_AIRPORT_ID%2CDIV1_AIRPORT_SEQ_ID%2CDIV1_WHEELS_ON%2CDIV1_TOTAL_GTIME%2CDIV1_LONGEST_GTIME%2CDIV1_WHEELS_OFF%2CDIV1_TAIL_NUM%2CDIV2_AIRPORT%2CDIV2_AIRPORT_ID%2CDIV2_AIRPORT_SEQ_ID%2CDIV2_WHEELS_ON%2CDIV2_TOTAL_GTIME%2CDIV2_LONGEST_GTIME%2CDIV2_WHEELS_OFF%2CDIV2_TAIL_NUM%2CDIV3_AIRPORT%2CDIV3_AIRPORT_ID%2CDIV3_AIRPORT_SEQ_ID%2CDIV3_WHEELS_ON%2CDIV3_TOTAL_GTIME%2CDIV3_LONGEST_GTIME%2CDIV3_WHEELS_OFF%2CDIV3_TAIL_NUM%2CDIV4_AIRPORT%2CDIV4_AIRPORT_ID%2CDIV4_AIRPORT_SEQ_ID%2CDIV4_WHEELS_ON%2CDIV4_TOTAL_GTIME%2CDIV4_LONGEST_GTIME%2CDIV4_WHEELS_OFF%2CDIV4_TAIL_NUM%2CDIV5_AIRPORT%2CDIV5_AIRPORT_ID%2CDIV5_AIRPORT_SEQ_ID%2CDIV5_WHEELS_ON%2CDIV5_TOTAL_GTIME%2CDIV5_LONGEST_GTIME%2CDIV5_WHEELS_OFF%2CDIV5_TAIL_NUM&grouplist=&suml=&sumRegion=&filter1=title%3D&filter2=title%3D&geo=All%A0&time=January&timename=Month&GEOGRAPHY=All&XYEAR=2019&FREQUENCY=1&VarName=YEAR&VarDesc=Year&VarType=Num&VarName=QUARTER&VarDesc=Quarter&VarType=Num&VarName=MONTH&VarDesc=Month&VarType=Num&VarName=DAY_OF_MONTH&VarDesc=DayofMonth&VarType=Num&VarName=DAY_OF_WEEK&VarDesc=DayOfWeek&VarType=Num&VarName=FL_DATE&VarDesc=FlightDate&VarType=Char&VarName=OP_UNIQUE_CARRIER&VarDesc=Reporting_Airline&VarType=Char&VarName=OP_CARRIER_AIRLINE_ID&VarDesc=DOT_ID_Reporting_Airline&VarType=Num&VarName=OP_CARRIER&VarDesc=IATA_CODE_Reporting_Airline&VarType=Char&VarName=TAIL_NUM&VarDesc=Tail_Number&VarType=Char&VarName=OP_CARRIER_FL_NUM&VarDesc=Flight_Number_Reporting_Airline&VarType=Char&VarName=ORIGIN_AIRPORT_ID&VarDesc=OriginAirportID&VarType=Num&VarName=ORIGIN_AIRPORT_SEQ_ID&VarDesc=OriginAirportSeqID&VarType=Num&VarName=ORIGIN_CITY_MARKET_ID&VarDesc=OriginCityMarketID&VarType=Num&VarName=ORIGIN&VarDesc=Origin&VarType=Char&VarName=ORIGIN_CITY_NAME&VarDesc=OriginCityName&VarType=Char&VarName=ORIGIN_STATE_ABR&VarDesc=OriginState&VarType=Char&VarName=ORIGIN_STATE_FIPS&VarDesc=OriginStateFips&VarType=Char&VarName=ORIGIN_STATE_NM&VarDesc=OriginStateName&VarType=Char&VarName=ORIGIN_WAC&VarDesc=OriginWac&VarType=Num&VarName=DEST_AIRPORT_ID&VarDesc=DestAirportID&VarType=Num&VarName=DEST_AIRPORT_SEQ_ID&VarDesc=DestAirportSeqID&VarType=Num&VarName=DEST_CITY_MARKET_ID&VarDesc=DestCityMarketID&VarType=Num&VarName=DEST&VarDesc=Dest&VarType=Char&VarName=DEST_CITY_NAME&VarDesc=DestCityName&VarType=Char&VarName=DEST_STATE_ABR&VarDesc=DestState&VarType=Char&VarName=DEST_STATE_FIPS&VarDesc=DestStateFips&VarType=Char&VarName=DEST_STATE_NM&VarDesc=DestStateName&VarType=Char&VarName=DEST_WAC&VarDesc=DestWac&VarType=Num&VarName=CRS_DEP_TIME&VarDesc=CRSDepTime&VarType=Char&VarName=DEP_TIME&VarDesc=DepTime&VarType=Char&VarName=DEP_DELAY&VarDesc=DepDelay&VarType=Num&VarName=DEP_DELAY_NEW&VarDesc=DepDelayMinutes&VarType=Num&VarName=DEP_DEL15&VarDesc=DepDel15&VarType=Num&VarName=DEP_DELAY_GROUP&VarDesc=DepartureDelayGroups&VarType=Num&VarName=DEP_TIME_BLK&VarDesc=DepTimeBlk&VarType=Char&VarName=TAXI_OUT&VarDesc=TaxiOut&VarType=Num&VarName=WHEELS_OFF&VarDesc=WheelsOff&VarType=Char&VarName=WHEELS_ON&VarDesc=WheelsOn&VarType=Char&VarName=TAXI_IN&VarDesc=TaxiIn&VarType=Num&VarName=CRS_ARR_TIME&VarDesc=CRSArrTime&VarType=Char&VarName=ARR_TIME&VarDesc=ArrTime&VarType=Char&VarName=ARR_DELAY&VarDesc=ArrDelay&VarType=Num&VarName=ARR_DELAY_NEW&VarDesc=ArrDelayMinutes&VarType=Num&VarName=ARR_DEL15&VarDesc=ArrDel15&VarType=Num&VarName=ARR_DELAY_GROUP&VarDesc=ArrivalDelayGroups&VarType=Num&VarName=ARR_TIME_BLK&VarDesc=ArrTimeBlk&VarType=Char&VarName=CANCELLED&VarDesc=Cancelled&VarType=Num&VarName=CANCELLATION_CODE&VarDesc=CancellationCode&VarType=Char&VarName=DIVERTED&VarDesc=Diverted&VarType=Num&VarName=CRS_ELAPSED_TIME&VarDesc=CRSElapsedTime&VarType=Num&VarName=ACTUAL_ELAPSED_TIME&VarDesc=ActualElapsedTime&VarType=Num&VarName=AIR_TIME&VarDesc=AirTime&VarType=Num&VarName=FLIGHTS&VarDesc=Flights&VarType=Num&VarName=DISTANCE&VarDesc=Distance&VarType=Num&VarName=DISTANCE_GROUP&VarDesc=DistanceGroup&VarType=Num&VarName=CARRIER_DELAY&VarDesc=CarrierDelay&VarType=Num&VarName=WEATHER_DELAY&VarDesc=WeatherDelay&VarType=Num&VarName=NAS_DELAY&VarDesc=NASDelay&VarType=Num&VarName=SECURITY_DELAY&VarDesc=SecurityDelay&VarType=Num&VarName=LATE_AIRCRAFT_DELAY&VarDesc=LateAircraftDelay&VarType=Num&VarName=FIRST_DEP_TIME&VarDesc=FirstDepTime&VarType=Char&VarName=TOTAL_ADD_GTIME&VarDesc=TotalAddGTime&VarType=Num&VarName=LONGEST_ADD_GTIME&VarDesc=LongestAddGTime&VarType=Num&VarName=DIV_AIRPORT_LANDINGS&VarDesc=DivAirportLandings&VarType=Num&VarName=DIV_REACHED_DEST&VarDesc=DivReachedDest&VarType=Num&VarName=DIV_ACTUAL_ELAPSED_TIME&VarDesc=DivActualElapsedTime&VarType=Num&VarName=DIV_ARR_DELAY&VarDesc=DivArrDelay&VarType=Num&VarName=DIV_DISTANCE&VarDesc=DivDistance&VarType=Num&VarName=DIV1_AIRPORT&VarDesc=Div1Airport&VarType=Char&VarName=DIV1_AIRPORT_ID&VarDesc=Div1AirportID&VarType=Num&VarName=DIV1_AIRPORT_SEQ_ID&VarDesc=Div1AirportSeqID&VarType=Num&VarName=DIV1_WHEELS_ON&VarDesc=Div1WheelsOn&VarType=Char&VarName=DIV1_TOTAL_GTIME&VarDesc=Div1TotalGTime&VarType=Num&VarName=DIV1_LONGEST_GTIME&VarDesc=Div1LongestGTime&VarType=Num&VarName=DIV1_WHEELS_OFF&VarDesc=Div1WheelsOff&VarType=Char&VarName=DIV1_TAIL_NUM&VarDesc=Div1TailNum&VarType=Char&VarName=DIV2_AIRPORT&VarDesc=Div2Airport&VarType=Char&VarName=DIV2_AIRPORT_ID&VarDesc=Div2AirportID&VarType=Num&VarName=DIV2_AIRPORT_SEQ_ID&VarDesc=Div2AirportSeqID&VarType=Num&VarName=DIV2_WHEELS_ON&VarDesc=Div2WheelsOn&VarType=Char&VarName=DIV2_TOTAL_GTIME&VarDesc=Div2TotalGTime&VarType=Num&VarName=DIV2_LONGEST_GTIME&VarDesc=Div2LongestGTime&VarType=Num&VarName=DIV2_WHEELS_OFF&VarDesc=Div2WheelsOff&VarType=Char&VarName=DIV2_TAIL_NUM&VarDesc=Div2TailNum&VarType=Char&VarName=DIV3_AIRPORT&VarDesc=Div3Airport&VarType=Char&VarName=DIV3_AIRPORT_ID&VarDesc=Div3AirportID&VarType=Num&VarName=DIV3_AIRPORT_SEQ_ID&VarDesc=Div3AirportSeqID&VarType=Num&VarName=DIV3_WHEELS_ON&VarDesc=Div3WheelsOn&VarType=Char&VarName=DIV3_TOTAL_GTIME&VarDesc=Div3TotalGTime&VarType=Num&VarName=DIV3_LONGEST_GTIME&VarDesc=Div3LongestGTime&VarType=Num&VarName=DIV3_WHEELS_OFF&VarDesc=Div3WheelsOff&VarType=Char&VarName=DIV3_TAIL_NUM&VarDesc=Div3TailNum&VarType=Char&VarName=DIV4_AIRPORT&VarDesc=Div4Airport&VarType=Char&VarName=DIV4_AIRPORT_ID&VarDesc=Div4AirportID&VarType=Num&VarName=DIV4_AIRPORT_SEQ_ID&VarDesc=Div4AirportSeqID&VarType=Num&VarName=DIV4_WHEELS_ON&VarDesc=Div4WheelsOn&VarType=Char&VarName=DIV4_TOTAL_GTIME&VarDesc=Div4TotalGTime&VarType=Num&VarName=DIV4_LONGEST_GTIME&VarDesc=Div4LongestGTime&VarType=Num&VarName=DIV4_WHEELS_OFF&VarDesc=Div4WheelsOff&VarType=Char&VarName=DIV4_TAIL_NUM&VarDesc=Div4TailNum&VarType=Char&VarName=DIV5_AIRPORT&VarDesc=Div5Airport&VarType=Char&VarName=DIV5_AIRPORT_ID&VarDesc=Div5AirportID&VarType=Num&VarName=DIV5_AIRPORT_SEQ_ID&VarDesc=Div5AirportSeqID&VarType=Num&VarName=DIV5_WHEELS_ON&VarDesc=Div5WheelsOn&VarType=Char&VarName=DIV5_TOTAL_GTIME&VarDesc=Div5TotalGTime&VarType=Num&VarName=DIV5_LONGEST_GTIME&VarDesc=Div5LongestGTime&VarType=Num&VarName=DIV5_WHEELS_OFF&VarDesc=Div5WheelsOff&VarType=Char&VarName=DIV5_TAIL_NUM&VarDesc=Div5TailNum&VarType=Char'

os.makedirs('data', exist_ok=True)

for year in range(1990, 1985, -1):
    for month in range(1, 13, 1):
        print('downloading {}/{}...'.format(year, month))
        r = requests.post('https://www.transtats.bts.gov/DownLoad_Table.asp',
                          headers=headers, params=params,
                          data=data.replace('2019', str(year)).replace('Month+%3D1', 'Month+%3D{}'.format(month)), stream=True)

        with open("data/{}.{}.flights.csv.zip".format(year, month), 'wb') as f:
            for chunk in tqdm(r.iter_content(chunk_size=102400)):
                if chunk:
                    f.write(chunk)
        print('{}/{} done.'.format(year, month))
