/// <summary>
/// The do add data.
/// </summary>
/// <param name="data">
/// The data.
/// </param>
/// <param name="packageId">
/// The package id.
/// </param>
/// <param name="packageCreatedAt">
/// The package created at.
/// </param>
/// <param name="callback">
/// The callback.
/// </param>
private void DoAddData(IList<TravelRoute> data, string packageId, DateTime packageCreatedAt, ProgressDialog callback)
{
using (var connection = new SQLiteConnection(this._connectionString))
{
if (data != null && data.Count > 0)
{
long totalData = 0;
this.Logger.InfoFormat("Add new {0} travel routes into database", data.Count);
var getIdRouteCmd = new SQLiteCommand("SELECT LID FROM ROUTE WHERE SDEPARTURE=@sDept AND SDESTINATION=@sDest", connection);
getIdRouteCmd.Parameters.Add("@sDept", DbType.String);
getIdRouteCmd.Parameters.Add("@sDest", DbType.String);
var insertRouteCmd = new SQLiteCommand(
"INSERT INTO ROUTE(LID, SDEPARTURE, SDESTINATION) " + "VALUES(@lId, @sDept, @sDest)",
connection);
insertRouteCmd.Parameters.Add("@lId", DbType.Int64);
insertRouteCmd.Parameters.Add("@sDept", DbType.String);
insertRouteCmd.Parameters.Add("@sDest", DbType.String);
var getIdJourneyCmd = new SQLiteCommand(
"SELECT LID FROM JOURNEY WHERE LROUTEID=@lRouteId AND TDEPARTURE=@tDept AND TRETURN=@tRet",
connection);
getIdJourneyCmd.Parameters.Add("@lRouteId", DbType.Int64);
getIdJourneyCmd.Parameters.Add("@tDept", DbType.String);
getIdJourneyCmd.Parameters.Add("@tRet", DbType.String);
var insertJourneyCmd =
new SQLiteCommand(
"INSERT INTO JOURNEY(LID, LROUTEID, TDEPARTURE, TRETURN) " + "VALUES(@lId, @lRouteId, @tDept, @tRet)",
connection);
insertJourneyCmd.Parameters.Add("@lId", DbType.Int64);
insertJourneyCmd.Parameters.Add("@lRouteId", DbType.Int64);
insertJourneyCmd.Parameters.Add("@tDept", DbType.String);
insertJourneyCmd.Parameters.Add("@tRet", DbType.String);
var getIdJourneyDataCmd = new SQLiteCommand(
"SELECT LID FROM JOURNEY_DATA WHERE LJOURNEYID=@lJourneyId AND TUPDATE=@tUpdate",
connection);
getIdJourneyDataCmd.Parameters.Add("@lJourneyId", DbType.Int64);
getIdJourneyDataCmd.Parameters.Add("@tUpdate", DbType.String);
var insertJourneyDataCmd =
new SQLiteCommand(
"INSERT INTO JOURNEY_DATA(LID, LJOURNEYID, SCURRENCY, TUPDATE, BFLIGHT) "
+ "VALUES(@lId, @lJourneyId, @sCurrency, @tUpdate, @bFlight)",
connection);
insertJourneyDataCmd.Parameters.Add("@lId", DbType.Int64);
insertJourneyDataCmd.Parameters.Add("@lJourneyId", DbType.Int64);
insertJourneyDataCmd.Parameters.Add("@sCurrency", DbType.String);
insertJourneyDataCmd.Parameters.Add("@tUpdate", DbType.String);
insertJourneyDataCmd.Parameters.Add("@bFlight", DbType.Binary);
var updateJourneyDataCmd =
new SQLiteCommand("UPDATE JOURNEY_DATA " + "SET SCURRENCY = @sCurrency, BFLIGHT = @bFlight " + "WHERE LID = @lId", connection);
updateJourneyDataCmd.Parameters.Add("@lId", DbType.Int64);
updateJourneyDataCmd.Parameters.Add("@sCurrency", DbType.String);
updateJourneyDataCmd.Parameters.Add("@bFlight", DbType.Binary);
connection.Open();
using (var startCmd = new SQLiteCommand("BEGIN TRANSACTION", connection))
{
startCmd.ExecuteNonQuery(); // Begin transaction
}
long nextRouteId = GetMaxRowId(connection, "ROUTE"),
nextJourneyId = GetMaxRowId(connection, "JOURNEY"),
nextDataId = GetMaxRowId(connection, "JOURNEY_DATA");
int totalJourneys = data.Sum(r => r.Journeys.Count);
callback.SetRange(0, totalJourneys);
using (getIdJourneyCmd)
using (insertJourneyCmd)
using (getIdJourneyDataCmd)
using (insertJourneyDataCmd)
{
foreach (var r in data)
{
if (r.Departure == null || r.Destination == null)
{
continue;
}
var deptAirport = AirportDataProvider.FromIATA(r.Departure.IATA);
if (deptAirport == null)
{
continue;
}
var destAirport = AirportDataProvider.FromIATA(r.Destination.IATA);
if (destAirport == null)
{
continue;
}
getIdRouteCmd.Parameters["@sDept"].Value = deptAirport.IATA;
getIdRouteCmd.Parameters["@sDest"].Value = destAirport.IATA;
var dbRouteId = getIdRouteCmd.ExecuteScalar();
bool isNewRoute = dbRouteId == null || dbRouteId is DBNull;
if (isNewRoute)
{
// Create new route if it does not exist
insertRouteCmd.Parameters["@lId"].Value = ++nextRouteId;
insertRouteCmd.Parameters["@sDept"].Value = deptAirport.IATA;
insertRouteCmd.Parameters["@sDest"].Value = destAirport.IATA;
insertRouteCmd.ExecuteNonQuery();
}
else
{
nextRouteId = (long)dbRouteId; // Reuse existing route
}
callback.Text = "[" + deptAirport + "] - [" + destAirport + "]";
foreach (Journey j in r.Journeys)
{
if (callback.IsAborting)
{
return;
}
if (j.Data.Count < 1)
{
continue;
}
bool isNewJourney = true;
if (!isNewRoute)
{
getIdJourneyCmd.Parameters["@lRouteId"].Value = nextRouteId;
getIdJourneyCmd.Parameters["@tDept"].Value = j.DepartureDate.ToString(DATE_FORMAT);
getIdJourneyCmd.Parameters["@tRet"].Value = j.ReturnDate.ToString(DATE_FORMAT);
var dbJourneyId = getIdJourneyCmd.ExecuteScalar();
isNewJourney = dbJourneyId == null || dbJourneyId is DBNull;
if (!isNewJourney)
{
j.Id = (long)dbJourneyId; // Reuse existing journey
}
}
if (isNewJourney)
{
// Create new journey if it does not exist
j.Id = ++nextJourneyId;
insertJourneyCmd.Parameters["@lId"].Value = j.Id;
insertJourneyCmd.Parameters["@lRouteId"].Value = nextRouteId;
insertJourneyCmd.Parameters["@tDept"].Value = j.DepartureDate.ToString(DATE_FORMAT);
insertJourneyCmd.Parameters["@tRet"].Value = j.ReturnDate.ToString(DATE_FORMAT);
insertJourneyCmd.ExecuteNonQuery();
}
foreach (JourneyData journeyData in j.Data)
{
bool isNewJourneyData = true;
if (!isNewJourney)
{
getIdJourneyDataCmd.Parameters["@lJourneyId"].Value = j.Id;
getIdJourneyDataCmd.Parameters["@tUpdate"].Value =
journeyData.DataDate.ToUniversalTime().ToString(DATETIME_FORMAT, null);
var dbDataId = getIdJourneyDataCmd.ExecuteScalar();
isNewJourneyData = dbDataId == null || dbDataId is DBNull;
if (!isNewJourneyData)
{
journeyData.Id = (long)dbDataId;
}
}
if (isNewJourneyData)
{
// Create new journey data history if it does not exist
journeyData.Id = ++nextDataId;
insertJourneyDataCmd.Parameters["@lId"].Value = journeyData.Id;
insertJourneyDataCmd.Parameters["@lJourneyId"].Value = j.Id;
insertJourneyDataCmd.Parameters["@sCurrency"].Value = journeyData.Currency;
insertJourneyDataCmd.Parameters["@tUpdate"].Value =
journeyData.DataDate.ToUniversalTime().ToString(DATETIME_FORMAT, null);
insertJourneyDataCmd.Parameters["@bFlight"].Value = this._formatter.ToRaw(journeyData.Flights);
insertJourneyDataCmd.ExecuteNonQuery();
}
else
{
// Update the journey data if it already exists
updateJourneyDataCmd.Parameters["@lId"].Value = journeyData.Id;
updateJourneyDataCmd.Parameters["@sCurrency"].Value = journeyData.Currency;
updateJourneyDataCmd.Parameters["@bFlight"].Value = this._formatter.ToRaw(journeyData.Flights);
updateJourneyDataCmd.ExecuteNonQuery(); // Update existing journey data
}
}
callback.Increment(1);
totalData += j.Data.Count;
}
}
}
// Process the data package id
if (!string.IsNullOrEmpty(packageId) && !this.IsPackageImported(packageId, connection, callback))
{
this.Logger.Info("Register package ID " + packageId);
// TODO: Check this
// callback.Style = ProgressStyleMarquee;
using (
var insertPkgCmd =
new SQLiteCommand(
"INSERT INTO DATA_PACKAGE (SID, TCREATED, TINSERTED) " + " VALUES (@pkgId, @pkgCreatedAt, @pkgInsertedAt)",
connection))
{
insertPkgCmd.Parameters.Add(new SQLiteParameter("@pkgId", packageId));
insertPkgCmd.Parameters.Add(
new SQLiteParameter("@pkgCreatedAt", packageCreatedAt.ToUniversalTime().ToString(DATE_FORMAT)));
insertPkgCmd.Parameters.Add(new SQLiteParameter("@pkgInsertedAt", DateTime.UtcNow.ToString(DATE_FORMAT)));
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
insertPkgCmd.ExecuteNonQuery();
}
}
// Finally, commit the transaction
this.Logger.InfoFormat("Completed adding {0} journey data. Commiting", totalData);
// Completed inserting all journeys or inserted nothing
using (var endCmd = new SQLiteCommand("COMMIT TRANSACTION", connection))
{
endCmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Execute a delegate on a separate thread and show a progress dialog
/// </summary>
/// <param name="target">
/// The target.
/// </param>
/// <param name="title">
/// The title.
/// </param>
/// <param name="text">
/// The text.
/// </param>
/// <param name="threadName">
/// The thread Name.
/// </param>
/// <param name="style">
/// The style.
/// </param>
/// <param name="logger">
/// The logger.
/// </param>
/// <param name="action">
/// The action.
/// </param>
/// <param name="exceptionHandler">
/// The exception Handler.
/// </param>
/// <param name="finalHandler">
/// The final Handler.
/// </param>
/// <param name="notifyErrorInMsgBox">
/// The notify Error In Msg Box.
/// </param>
/// <param name="visible">
/// The visible.
/// </param>
public static void ExecuteTask(
IWin32Window target,
string title,
string text,
string threadName,
ProgressBarStyle style,
ILogger logger,
CallbackDelegate action,
CallbackExceptionDelegate exceptionHandler,
CallbackExceptionDelegate finalHandler,
bool notifyErrorInMsgBox,
bool visible)
{
using (var progressDialog = new ProgressDialog(title, text, style, true))
{
if (!visible)
{
progressDialog.WindowState = FormWindowState.Minimized;
}
ThreadPool.QueueUserWorkItem(
delegate (object param)
{
threadName = string.IsNullOrEmpty(threadName) ? title.Replace(" ", string.Empty) : threadName;
AppUtil.NameCurrentThread(threadName);
var callback = param as ProgressDialog;
Exception actionException = null;
try
{
action(callback);
}
catch (Exception ex)
{
actionException = ex;
if (!callback.IsAborting)
{
if (logger != null)
{
string currentTitle = callback.Title;
logger.Error((string.IsNullOrEmpty(currentTitle) ? null : currentTitle + ": ") + ex);
if (notifyErrorInMsgBox)
{
ExMessageBox.Show(
target,
"An error occured: " + ex.Message,
currentTitle,
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
if (exceptionHandler != null)
{
exceptionHandler(callback, ex);
}
else
{
throw;
}
}
}
finally
{
if (callback != null)
{
callback.End();
}
if (finalHandler != null)
{
finalHandler(callback, actionException);
}
}
},
progressDialog);
progressDialog.ShowDialog(target);
}
}