public static void Add(NpgsqlConnection conn, string airportName, string city, string state,
ConcurrentDictionary <string, Models.Dim_Airport> dictAirports,
ConcurrentDictionary <string, Models.Dim_City> dictCity,
ConcurrentDictionary <string, Models.Dim_State> dictState)
{
// Clean airport name
string cleanAirport = Clean(airportName);
if (cleanAirport != string.Empty && !dictAirports.ContainsKey(cleanAirport))
{
var tempState = state;
if (tempState == string.Empty)
{
var airportparts = airportName.Split(':');
if (airportparts.Any())
{
tempState = airportparts[0].Replace(city, "");
}
}
if (AirportRepository.Find(conn, cleanAirport, city, tempState, dictAirports, dictCity) == null)
{
var cityRec = CityRepository.Find(conn, city, tempState, dictCity, dictState);
if (cityRec != null)
{
// Write the airport to the database.
conn.Execute(airport_insert, new
{
airport_name = cleanAirport,
city_id = cityRec.city_id
});
// find to Add to hash table.
AirportRepository.Find(conn, cleanAirport, city, tempState, dictAirports, dictCity);
}
else
{
Console.WriteLine($"Could not add airport: {airportName}");
}
}
}
}
public static void Add(NpgsqlConnection conn, Row row,
ConcurrentDictionary <string, Models.Dim_Airline> dictAirline,
ConcurrentDictionary <string, Models.Dim_Airport> dictAirport,
ConcurrentDictionary <string, Models.Dim_City> dictCity,
ConcurrentDictionary <string, Models.Dim_State> dictState,
ConcurrentDictionary <string, Models.Dim_Tail> dictTail)
{
// Convert Distance.
string tempDistance = (string)row["Distance"];
int tempDistanceInt = 0;
string tempDistanceUnits = string.Empty;
string tempDistanceGroup = string.Empty;
var tempDistArray = tempDistance.Split(' ');
string unit = string.Empty;
if (tempDistArray.Length == 2)
{
tempDistanceInt = Convert.ToInt32(tempDistArray[0]);
tempDistanceUnits = tempDistArray[1].ToString().Trim();
tempDistanceGroup = GetDistanceGroup(tempDistanceInt);
}
else
{
throw new Exception("Invalid disance");
}
// Find codes
var airline = dictAirline.Where(w => w.Key == (string)row["AirlineCode"]).FirstOrDefault().Value;;
var tail = dictTail.Where(w => w.Key == TailRepository.CleanTail((string)row["TailNum"])).FirstOrDefault().Value;
var origin_airport = dictAirport.Where(w => w.Key == AirportRepository.Clean((string)row["OriginAirportName"])).FirstOrDefault().Value;
var dest_airport = dictAirport.Where(w => w.Key == AirportRepository.Clean((string)row["DestAirportName"])).FirstOrDefault().Value;
// Write the airline to the database.
conn.Execute(fact_insert, new
{
transaction_id = row["TransactionId"],
flight_date = row["FlightDate"],
airline_id = airline.airline_id,
tail_id = tail.tail_id,
flight_num = row["FlightNum"],
origin_airport_id = origin_airport.airport_id,
destination_airport_id = dest_airport.airport_id,
crs_dep_time = row["CrsDepTime"],
dep_time = row["DepTime"],
dep_delay = row["DepDelay"],
taxi_out = row["TaxiOut"],
wheels_off = row["WheelsOff"],
wheels_on = row["WheelsOn"],
taxi_in = row["TaxiIn"],
crs_arr_time = row["CrsArrTime"],
arr_time = row["ArrTime"],
arr_delay = row["ArrDelay"],
crs_elapsed_time = row["CrsElapsedTime"],
actual_elapsed_time = row["ActualElapsedTime"],
cancelled = row["Cancelled"],
diverted = row["Diverted"],
distance = tempDistanceInt,
distance_unit = tempDistanceUnits,
distance_group = GetDistanceGroup(tempDistanceInt),
long_dep_delay = ((int)row["DepDelay"] > 15),
arr_next_day = (DateTime.Compare((DateTime)row["ArrTime"], (DateTime)row["DepTime"]) < 0)
});
}
