/// <summary> /// The load data. /// </summary> /// <param name="journeys"> /// The journeys. /// </param> /// <param name="loadHistory"> /// The load history. /// </param> /// <param name="callback"> /// The callback. /// </param> public void LoadData(IList<Journey> journeys, bool loadHistory, IProgressCallback callback) { this.Logger.DebugFormat("Load data for {0} journeys [{1}]", journeys.Count, loadHistory ? "H" : null); foreach (var j in journeys) { j.Data.Clear(); } string condition = GenerateCondition(journeys); using (var connection = new SQLiteConnection(this._connectionString)) { string selectSql = "SELECT LID, LJOURNEYID, SCURRENCY, BFLIGHT, " + (loadHistory ? "TUPDATE" : "MAX(TUPDATE) TUPDATE") + " FROM JOURNEY_DATA " + " WHERE " + condition + (loadHistory ? string.Empty : " GROUP BY (LJOURNEYID)"); using (var getFlightsCmd = new SQLiteCommand(selectSql, connection)) { connection.Open(); using (var reader = getFlightsCmd.ExecuteReader()) { if (reader.HasRows) { if (reader.HasRows) { int iId = reader.GetOrdinal("LID"); int iJourneyId = reader.GetOrdinal("LJOURNEYID"); int iCurrency = reader.GetOrdinal("SCURRENCY"); int iUpdate = reader.GetOrdinal("TUPDATE"); int iFlight = reader.GetOrdinal("BFLIGHT"); while (reader.Read()) { var flights = this._formatter.FromRaw<List<Flight>>((byte[])reader[iFlight]); if (flights != null && flights.Count > 0) { long dataId = reader.GetInt64(iId); long journeyId = reader.GetInt64(iJourneyId); string currency = reader.GetString(iCurrency); string dataDateStr = reader.GetString(iUpdate); DateTime dataDate = DateTime.ParseExact( dataDateStr, DATETIME_FORMAT, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal); var newData = new JourneyData(dataId, currency, dataDate); newData.AddFlights(flights); foreach (var j in journeys) { if (j.Id == journeyId) { j.AddData(newData); break; } } } } } } } } } }
/// <summary> /// The load data. /// </summary> /// <param name="route"> /// The route. /// </param> /// <param name="loadJourneyData"> /// The load journey data. /// </param> /// <param name="loadHistory"> /// The load history. /// </param> /// <param name="loadFlights"> /// The load flights. /// </param> /// <param name="callback"> /// The callback. /// </param> /// <exception cref="ArgumentException"> /// </exception> public void LoadData(TravelRoute route, bool loadJourneyData, bool loadHistory, bool loadFlights, IProgressCallback callback) { this.Logger.DebugFormat( "Load data for route [{0}-{1}] [{2}{3}{4}]", route.Departure.IATA, route.Destination.IATA, loadJourneyData ? "D" : null, loadHistory ? "H" : null, loadFlights ? "F" : null); using (var connection = new SQLiteConnection(this._connectionString)) { if (route.Id < 1) { throw new ArgumentException("Invalid Route Id"); } route.Journeys.Clear(); string selectSql = "SELECT J.LID, J.TDEPARTURE, J.TRETURN" + (loadJourneyData ? (", D.LID DATAID, D.SCURRENCY, " + (loadHistory ? "D.TUPDATE" : "MAX(D.TUPDATE) TUPDATE") + (loadFlights ? ", D.BFLIGHT" : string.Empty)) : string.Empty) + " FROM JOURNEY J" + (loadJourneyData ? ", JOURNEY_DATA D " : string.Empty) + " WHERE J.LROUTEID = @lRouteId " + (loadJourneyData ? " AND D.LJOURNEYID = J.LID " + (loadHistory ? string.Empty : " GROUP BY (D.LJOURNEYID) ") : string.Empty); using (var getJourneyCmd = new SQLiteCommand(selectSql, connection)) { getJourneyCmd.Parameters.AddWithValue("@lRouteId", route.Id); connection.Open(); using (var reader = getJourneyCmd.ExecuteReader()) { if (reader.HasRows) { int iId = reader.GetOrdinal("LID"); int iDeparture = reader.GetOrdinal("TDEPARTURE"); int iReturn = reader.GetOrdinal("TRETURN"); int iUpdate = reader.GetOrdinal("TUPDATE"); int iCurrency = loadJourneyData ? reader.GetOrdinal("SCURRENCY") : -1; int iDataId = loadJourneyData ? reader.GetOrdinal("DATAID") : -1; int iFlight = loadJourneyData ? reader.GetOrdinal("BFLIGHT") : -1; while (reader.Read()) { long journeyId = reader.GetInt64(iId); Journey journey = null; var allJourneys = route.Journeys; foreach (var j in allJourneys) { if (j.Id == journeyId) { journey = j; break; } } if (journey == null) { var deptDate = DateTime.ParseExact(reader.GetString(iDeparture), DATE_FORMAT, CultureInfo.InvariantCulture); var retDate = DateTime.ParseExact(reader.GetString(iReturn), DATE_FORMAT, CultureInfo.InvariantCulture); journey = new Journey(journeyId, route, deptDate, retDate); route.AddJourney(journey); } if (loadJourneyData) { var dataDate = DateTime.ParseExact( reader.GetString(iUpdate), DATETIME_FORMAT, CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal); var newData = new JourneyData(reader.GetInt64(iDataId), reader.GetString(iCurrency), dataDate); if (loadFlights) { var dbFlights = this._formatter.FromRaw<List<Flight>>((byte[])reader[iFlight]); if (dbFlights != null && dbFlights.Count > 0) { newData.AddFlights(dbFlights); } } journey.AddData(newData); } } } } } } }