/// <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);
}
}
}
}
}
}
}
