public int InsertNewLine(DsBusWeb.LinesRow dr)
{
ExecuteNonQuery(string.Format("INSERT INTO Lines(LineName, Culture) VALUES({0}, {1})",
OleDbStrHelper.getParamStr(dr.LineName),
OleDbStrHelper.getParamStr(dr.Culture)));
return(Convert.ToInt32(ExecuteScalar("SELECT MAX(LineID) FROM Lines")));
}
public int GetStopIDByOwnerAndStopID(int stopID, string owner)
{
_db.ExecuteCommand.CommandText = string.Format("SELECT StopID FROM Stops WHERE StopID = {0} AND Owner = {1}",
OleDbStrHelper.getParamStr(stopID),
OleDbStrHelper.getParamStr(owner));
return(Convert.ToInt32(_db.ExecuteScalar()));
}
/// <summary>
/// Move all stops under these duplicated lines, then delete these duplicated lines.
/// </summary>
/// <param name="dupLines">List of duplicated lines</param>
/// <param name="primaryLine">The primary line which will be attached within these moving stops.</param>
private void DoMerging(List <DsBusWeb.vwDupLineDetailRow> dupLines, DsBusWeb.vwDupLineDetailRow primaryLine)
{
if (dupLines.Count > 0)
{
#region do merging
try
{
_db.BeginTrans();
foreach (var line in dupLines)
{
_db.ExecuteCommand.CommandText = string.Format("UPDATE Line2Stop SET LineID = {0} WHERE LineID = {1}",
OleDbStrHelper.getParamStr(primaryLine.LineID),
OleDbStrHelper.getParamStr(line.LineID));
_db.Execute(); // Move all stop under the line to the primary line.
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM Lines WHERE LineID = {0}",
OleDbStrHelper.getParamStr(line.LineID));
_db.Execute(); // Delete the duplicate line.
line.Delete(); // Marked as deleted row means it had been merged.
}
_db.CommitTrans();
}
catch
{
_db.RollBack();
throw;
}
finally
{
_db.Close();
}
#endregion
}
}
public DsBusWeb.LinesDataTable GetLinesList(DateTime stpBgnDT, DateTime stpEndDT)
{
DsBusWeb.LinesDataTable dt = new DsBusWeb.LinesDataTable();
FillDt(string.Format("SELECT l.*, (SELECT COUNT(1) FROM Line2Stop l2s WHERE l2s.LineID = l.LineID) AS StopCount FROM Lines l WHERE l.LineID IN (SELECT l2s.LineID FROM Line2Stop l2s INNER JOIN Stops s ON l2s.StopID = s.StopID WHERE s.DateCreated BETWEEN {0} AND {1}) ORDER BY l.LineName",
OleDbStrHelper.getParamStr(stpBgnDT),
OleDbStrHelper.getParamStr(stpEndDT)), dt);
return(dt);
}
public DsBusWeb.LinesDataTable GetLinesList(double Longitude, double Latitude, double radius, string owner)
{
DsBusWeb.LinesDataTable dt = new DsBusWeb.LinesDataTable();
FillDt(string.Format("SELECT DISTINCT l.* FROM (Lines l INNER JOIN Line2Stop l2s ON l.LineID = l2s.LineID) INNER JOIN (SELECT StopID FROM Stops WHERE (Owner = {3}) OR (ABS(Longitude - {0}) < {2} AND ABS(Latitude - {1}) < {2})) tmp ON l2s.StopID = tmp.StopID ORDER BY LineName",
OleDbStrHelper.getParamStr(new decimal(Longitude)),
OleDbStrHelper.getParamStr(new decimal(Latitude)),
OleDbStrHelper.getParamStr(new decimal(radius)),
OleDbStrHelper.getParamStr(owner)), dt);
return(dt);
}
public void LogAUsageCase(DsBusWeb.UsageStatisticRow dr)
{
try
{
ExecuteNonQuery(string.Format("INSERT INTO UsageStatistic(UserCode, Longitude, Latitude, Radius, Device) VALUES({0}, {1}, {2}, {3}, {4})",
OleDbStrHelper.getParamStr(dr.UserCode),
OleDbStrHelper.getParamStr(new decimal(dr.Longitude)),
OleDbStrHelper.getParamStr(new decimal(dr.Latitude)),
OleDbStrHelper.getParamStr(new decimal(dr.Radius)),
OleDbStrHelper.getParamStr(dr.Device)
));
}
catch (Exception ex) { Trace.WriteLine(ex); }
}
public DsBusWeb.StopsDataTable GetStopsByLineID(int lineID, string owner)
{
DsBusWeb.StopsDataTable dt = new DsBusWeb.StopsDataTable();
FillDt(string.Format("SELECT s.*, sr.RatingGood, sr.RatingBad FROM (Stops s INNER JOIN Line2Stop l2s ON s.StopID = l2s.StopID) INNER JOIN StopRating sr ON s.StopID = sr.StopID WHERE l2s.LineID={0} ORDER BY StopName",
OleDbStrHelper.getParamStr(lineID)),
dt);
if (!string.IsNullOrEmpty(owner))
{
foreach (var dr in dt)
{
if (dr.Owner == owner)
{
dr.StopName = string.Format("{0}(*)", dr.StopName);
}
}
}
return(dt);
}
public int InsertNewStop(DsBusWeb.StopsRow dr)
{
_db.Open();
_db.BeginTrans();
int stopID = 0;
try
{
_db.ExecuteCommand.CommandText = string.Format("INSERT INTO Stops(StopName, Longitude, Latitude, CreatorLongitude, CreatorLatitude, Owner, Culture) VALUES({0}, {1}, {2}, {3}, {4}, {5}, {6})",
OleDbStrHelper.getParamStr(dr.StopName),
OleDbStrHelper.getParamStr(new decimal(dr.Longitude)),
OleDbStrHelper.getParamStr(new decimal(dr.Latitude)),
OleDbStrHelper.getParamStr(new decimal(dr.CreatorLongitude)),
OleDbStrHelper.getParamStr(new decimal(dr.CreatorLatitude)),
OleDbStrHelper.getParamStr(dr.Owner),
OleDbStrHelper.getParamStr(dr.Culture));
_db.Execute();
_db.ExecuteCommand.CommandText = "SELECT MAX(StopID) FROM Stops";
stopID = Convert.ToInt32(_db.ExecuteScalar());
_db.ExecuteCommand.CommandText = string.Format("INSERT INTO StopRating(StopID) VALUES({0})",
OleDbStrHelper.getParamStr(stopID));
_db.Execute();
_db.CommitTrans();
}
catch
{
_db.RollBack();
throw;
}
finally
{
_db.Close();
}
return(stopID);
}
/// <summary>
/// Delete duplicate stop info
/// </summary>
/// <param name="dupStops">List of duplicated stops</param>
private void DoMerging(List <DsBusWeb.vwDupStopDetailRow> dupStops)
{
if (dupStops.Count > 0)
{
#region do merging
try
{
_db.BeginTrans();
foreach (var stop in dupStops)
{
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM Line2Stop WHERE StopID = {0}",
OleDbStrHelper.getParamStr(stop.StopID));
_db.Execute(); // Move all stop under the line to the primary line.
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM StopRating WHERE StopID = {0}",
OleDbStrHelper.getParamStr(stop.StopID));
_db.Execute(); // Delete the duplicate line.
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM Stops WHERE StopID = {0}",
OleDbStrHelper.getParamStr(stop.StopID));
_db.Execute(); // Delete the duplicate line.
}
_db.CommitTrans();
}
catch
{
_db.RollBack();
throw;
}
finally
{
_db.Close();
}
#endregion
}
}
public void UpdateLine(DsBusWeb.LinesRow dr)
{
ExecuteNonQuery(string.Format("UPDATE Lines SET LineName={0} WHERE LineID={1}",
OleDbStrHelper.getParamStr(dr.LineName),
OleDbStrHelper.getParamStr(dr.LineID)));
}
public void DeleteLineByID(int lineID)
{
try
{
_db.BeginTrans();
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM StopRating WHERE StopID IN (SELECT StopID FROM Line2Stop WHERE LineID = {0})", OleDbStrHelper.getParamStr(lineID));
_db.Execute();
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM Line2Stop WHERE LineID = {0}", OleDbStrHelper.getParamStr(lineID));
_db.Execute();
_db.ExecuteCommand.CommandText = "DELETE FROM Stops WHERE StopID NOT IN (SELECT StopID FROM Line2Stop)";
_db.Execute();
_db.ExecuteCommand.CommandText = string.Format("DELETE FROM Lines WHERE LineID = {0}", OleDbStrHelper.getParamStr(lineID));
_db.Execute();
_db.CommitTrans();
}
catch
{
_db.RollBack();
throw;
}
finally
{
_db.Close();
}
}
public void RateStopBad(int stopID)
{
ExecuteNonQuery(string.Format("UPDATE StopRating SET RatingBad=RatingBad + 1 WHERE StopID={0}", OleDbStrHelper.getParamStr(stopID)));
}
public void InsertNewLine2StopRelation(DsBusWeb.Line2StopRow dr)
{
ExecuteNonQuery(string.Format("INSERT INTO Line2Stop(LineID, StopID) VALUES({0}, {1})",
OleDbStrHelper.getParamStr(dr.LineID),
OleDbStrHelper.getParamStr(dr.StopID)));
}
public void UpdateStop(DsBusWeb.StopsRow dr)
{
ExecuteNonQuery(string.Format("UPDATE Stops SET StopName={0} WHERE StopID={1}",
OleDbStrHelper.getParamStr(dr.StopName),
OleDbStrHelper.getParamStr(dr.StopID)));
}
