public void Delete(TripRecord trip)
{
string strSQL;
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
strSQL = "delete from Trip where userID = '" + trip.userID + "' and cityNo = '" + trip.cityNo + "'";
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL;
m_oleCommand.ExecuteNonQuery();
}
catch (InvalidOperationException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to delete trip: " + trip.userID + " " + trip.cityNo);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to delete trip: " + trip.userID + " " + trip.cityNo);
}
catch
{
throw new Common.TripDBException("Failed to delete trip: " + trip.userID + " " + trip.cityNo);
}
}
public ActionResult DeleteConfirmed(int id)
{
TripRecord tripRecord = db.TripRecords.Find(id);
db.TripRecords.Remove(tripRecord);
db.SaveChanges();
return(RedirectToAction("Index"));
}
public async Task <ActionResult> DeleteConfirmed(int id)
{
TripRecord tripRecord = await db.TripRecords.FindAsync(id);
db.TripRecords.Remove(tripRecord);
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
public void UpdateTrip(TripRecord b_trip, TripRecord m_trip)//SIGN
{
try
{
m_tripRepository.Update(b_trip, m_trip);
}
catch (Common.TripDBException ex)
{
throw new Common.TripDBException(ex.Message);
}
}
public void DeleteTrip(TripRecord trip)
{
try
{
m_tripRepository.Delete(trip);
}
catch (Common.TripDBException ex)
{
throw new Common.TripDBException(ex.Message);
}
}
public ActionResult Edit([Bind(Include = "Id,CampingTripId,CampingPlaceId,StartTime,EndTime,Comments")] TripRecord tripRecord)
{
if (ModelState.IsValid)
{
db.Entry(tripRecord).State = EntityState.Modified;
db.SaveChanges();
return(RedirectToAction("Index"));
}
ViewBag.CampingPlaceId = new SelectList(db.CampingPlaces, "Id", "Name", tripRecord.CampingPlaceId);
ViewBag.CampingTripId = new SelectList(db.CampingTrips, "Id", "Id", tripRecord.CampingTripId);
return(View(tripRecord));
}
/// <summary>
/// 行驶记录
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button6_Click(object sender, EventArgs e)
{
splitContainer1.Panel2.Controls.Clear();
TripRecord allTrip = new TripRecord();
allTrip.TopLevel = false;
// allTrip.FormBorderStyle = FormBorderStyle.None;
// allTrip.Parent = this.splitContainer1.Panel2;
this.splitContainer1.Panel2.Controls.Add(allTrip); //add the fs form to the panel2
allTrip.Dock = DockStyle.Fill;
allTrip.Show();
}
public List <TripRecord> GetTrips(string userID)
{
string strSQL;
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
strSQL = "select ID, userID, cityNo, arriveTime, memo, name from Trip where userID = '" + userID + "'";
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL;
MySqlDataReader oleReader = m_oleCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(oleReader);
oleReader.Close();
List <TripRecord> trs = null;
if (dt.Rows.Count > 0)
{
trs = new List <TripRecord>();
foreach (DataRow dr in dt.Rows)
{
TripRecord tr = new TripRecord();
tr.ID = dr[0].ToString();
tr.userID = dr[1].ToString();
tr.cityNo = dr[2].ToString();
tr.arriveTime = Convert.ToDateTime(dr[3].ToString());
tr.memo = dr[4].ToString();
tr.memo = dr[5].ToString();
trs.Add(tr);
}
}
return(trs);
}
catch (InvalidOperationException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to get trips: " + userID);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to get trips: " + userID);
}
catch
{
throw new Common.TripDBException("Failed to get trip: " + userID + " " + userID);
}
}
public async Task <ActionResult> Edit([Bind(Include = "Id,TripId,Odometer,StoppingPoint,ArrivalTime,Depart,PassengersIn,PassengersOut,DriverId,Remarks")] TripRecord tripRecord)
{
if (ModelState.IsValid)
{
db.Entry(tripRecord).State = EntityState.Modified;
await db.SaveChangesAsync();
return(RedirectToAction("Index"));
}
ViewBag.DriverId = new SelectList(db.Drivers, "DriverId", "Name", tripRecord.DriverId);
ViewBag.TripId = new SelectList(db.Trips, "Id", "ServiceNo", tripRecord.TripId);
return(View(tripRecord));
}
// GET: TripRecords/Details/5
public ActionResult Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
TripRecord tripRecord = db.TripRecords.Find(id);
if (tripRecord == null)
{
return(HttpNotFound());
}
return(View(tripRecord));
}
// GET: TripRecords/Details/5
public async Task <ActionResult> Details(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
TripRecord tripRecord = await db.TripRecords.FindAsync(id);
if (tripRecord == null)
{
return(HttpNotFound());
}
return(View(tripRecord));
}
public EditTripForm(TripRecord trip, bool bAdd)
{
InitializeComponent();
m_trip = trip;
m_bAdd = bAdd;
IUserRepository userRepository = TripApplication.Instance.DBFactory.CreateUserRepository();
ITripRepository tripRepository = TripApplication.Instance.DBFactory.CreateTripRepository();
ICityRepository cityRepository = TripApplication.Instance.DBFactory.CreateCityRepository();
m_userBLL = new UserBLL(userRepository);
m_tripBLL = new TripBLL(tripRepository);
m_cityBLL = new CityBLL(cityRepository);
}
// GET: TripRecords/Edit/5
public ActionResult Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
TripRecord tripRecord = db.TripRecords.Find(id);
if (tripRecord == null)
{
return(HttpNotFound());
}
ViewBag.CampingPlaceId = new SelectList(db.CampingPlaces, "Id", "Name", tripRecord.CampingPlaceId);
ViewBag.CampingTripId = new SelectList(db.CampingTrips, "Id", "Id", tripRecord.CampingTripId);
return(View(tripRecord));
}
private void btnEdit_Click(object sender, EventArgs e)
{
TripRecord trip = new TripRecord();
trip.userID = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
trip.name = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
trip.arriveTime = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[2].Value.ToString());
trip.memo = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
EditTripForm fm = new EditTripForm(trip, false);
if (fm.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
FillGrid();
}
}
// GET: TripRecords/Edit/5
public async Task <ActionResult> Edit(int?id)
{
if (id == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
TripRecord tripRecord = await db.TripRecords.FindAsync(id);
if (tripRecord == null)
{
return(HttpNotFound());
}
ViewBag.DriverId = new SelectList(db.Drivers, "DriverId", "Name", tripRecord.DriverId);
ViewBag.TripId = new SelectList(db.Trips, "Id", "ServiceNo", tripRecord.TripId);
return(View(tripRecord));
}
public TripRecord GetTrip(string userID, string cityNo)
{
string strSQL;
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
strSQL = "select userID, cityNo, arriveTime, memo, name from Trip where userID = '" + userID + "' and cityNo = '" + cityNo + "'";
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL;
MySqlDataReader oleReader = m_oleCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(oleReader);
oleReader.Close();
TripRecord tr = null;
if (dt.Rows.Count > 0)
{
tr = new TripRecord();
tr.userID = dt.Rows[0][0].ToString();
tr.cityNo = dt.Rows[0][1].ToString();
tr.arriveTime = Convert.ToDateTime(dt.Rows[0][2].ToString());
tr.memo = dt.Rows[0][3].ToString();
tr.memo = dt.Rows[0][4].ToString();
}
return(tr);
}
catch (InvalidOperationException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to get trip: " + userID + " " + cityNo);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to get trip: " + userID + " " + cityNo);
}
catch
{
throw new Common.TripDBException("Failed to get trip: " + userID + " " + cityNo);
}
}
//Parser for Yellow Taxi Trips 2015 (All) & 2016 (first 6 months)
public override TripRecord ParseTokens(Dictionary <string, string> row)
{
TripRecord record = new TripRecord();
record.ID = Idx++;
record.Distance = double.Parse(row["trip_distance"]);
record.Pickup_Longitude = float.Parse(row["pickup_longitude"]);
record.Pickup_Latitude = float.Parse(row["pickup_latitude"]);
record.Dropoff_Longitude = float.Parse(row["dropoff_longitude"]);
record.Dropoff_Latitude = float.Parse(row["dropoff_latitude"]);
string key = row.Keys.Where(x => x.Contains("pickup_datetime"))?.First();
record.TimeStamp = DateTime.Parse(row[key]);
return(record);
}
public void Add(TripRecord trip)
{
StringBuilder strSQL = new StringBuilder("insert into Trip(ID, UserID, CityNo, ArriveTime, name, memo) values (");//SIGN:ID自动分配
GetID();
strSQL.Append(TripID + ", ");
strSQL.Append(trip.userID + ", ");
strSQL.Append(trip.cityNo + ", '");
strSQL.Append(trip.arriveTime.ToString() + "','");
strSQL.Append(trip.name + "','");
strSQL.Append(trip.memo + "')");//SIGN:之前memo会出错
//string x = strSQL.ToString();
// strSQL.Append(trip.memo + "')");
// StringBuilder strSQL = new StringBuilder("insert into Trip(UserID, CityNo, ArriveTime, Memo) values ('430102196806092413', '430101','" + trip.arriveTime.ToString() + "',' ')");
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL.ToString();
m_oleCommand.ExecuteNonQuery();
m_oleConnection.Close();
}
catch (InvalidOperationException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to add trip: " + trip.userID + " " + trip.cityNo);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to add trip: " + trip.userID + " " + trip.cityNo);
}
catch
{
throw new Common.TripDBException("Failed to add trip: " + trip.userID + " " + trip.cityNo);
}
}
private void btnConfirm_Click(object sender, EventArgs e)
{
/* m_user.userID = txtCode.Text;
* m_user.name = txtName.Text;
* m_user.age = Convert.ToInt32(m_user.age);
* m_user.sex = cboSex.Text;
* m_user.cityNo = cboPlace.Text.Substring(0, 6);
* m_user.summary = txtSummary.Text;*/
string TuserID = m_trip.userID; string Tname = m_trip.name; DateTime TtxtArriveTime = m_trip.arriveTime; string Tmemo = m_trip.memo;
TripRecord b_trip = new TripRecord();//SIGN：点进更新框时，用于记录原数据的对象
b_trip.arriveTime = TtxtArriveTime; b_trip.userID = TuserID; b_trip.name = Tname; b_trip.memo = Tmemo;
m_trip.ID = "0";
m_trip.userID = cboUser.Text.Substring(0, 18); //SIGN：这里必须是个人ID相应的位数
string[] arr = cboPlace.Text.Split(' '); //SIGN:逗号分割字符串
m_trip.name = arr[1];
m_trip.cityNo = arr[0];
m_trip.arriveTime = Convert.ToDateTime(txtArriveTime.Text);
m_trip.memo = txtMemo.Text;
try
{
if (m_bAdd)
{
m_tripBLL.AddTrip(m_trip);
}
else
{
m_tripBLL.UpdateTrip(b_trip, m_trip);
}
}
catch (TripDBException ex)
{
MessageBox.Show(ex.Message);
}
this.DialogResult = System.Windows.Forms.DialogResult.OK;
Close();
}
private static void AddTrip(List <ResultRecord> records, int paraBusNo, BusStop paraBoardStop, BusStop paraAlightStop)
{
TripRecord tripRecord = new TripRecord()
{
boardStop = paraBoardStop, alightStop = paraAlightStop
};
if (records.FindIndex(item => item.busNo == paraBusNo) < 0)
{
ResultRecord resultRecord = new ResultRecord()
{
busNo = paraBusNo, trips = new HashSet <TripRecord>()
};
resultRecord.trips.Add(tripRecord);
records.Add(resultRecord);
}
else
{
HashSet <TripRecord> trips = records.Find(item => item.busNo == paraBusNo).trips;
trips.Add(tripRecord);
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
string userID = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string cityNo = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
TripRecord trip = new TripRecord();
trip.userID = userID;
trip.cityNo = cityNo;
if (MessageBox.Show("确认删除这条记录?", "信息提示", MessageBoxButtons.OKCancel) == System.Windows.Forms.DialogResult.OK)
{
try
{
m_tripBLL.DeleteTrip(trip);
FillGrid();
}
catch (TripDBException ex)
{
MessageBox.Show(ex.Message);
}
}
}
public void Update(TripRecord b_trip, TripRecord m_trip)//SIGN
{
StringBuilder strSQL = new StringBuilder("update trip set userID='");
strSQL.Append(m_trip.userID + "', cityNo='");
strSQL.Append(m_trip.cityNo + "', arriveTime='");
strSQL.Append(m_trip.arriveTime.ToString() + /**/ "', name='");
strSQL.Append(m_trip.name.ToString() + /**/ "', memo='");
strSQL.Append(m_trip.memo + /**/ "' where name = '" + b_trip.name + "' and UserID='" + b_trip.userID + "' and arriveTime='" + b_trip.arriveTime.ToString() + "'and memo='" + b_trip.memo + "'");
string x = strSQL.ToString();
//strSQL.Append(trip.memo + "' where userID = '" + trip.userID + "' and cityNo = '" + trip.cityNo + "'");
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL.ToString();
m_oleCommand.ExecuteNonQuery();
m_oleConnection.Close();
}
catch (InvalidOperationException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to update trip: " + m_trip.userID + " " + m_trip.cityNo);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.TripDBException(ex.Message + " " + "Failed to update trip: " + m_trip.userID + " " + m_trip.cityNo);
}
catch
{
throw new Common.TripDBException("Failed to update trip: " + m_trip.userID + " " + m_trip.cityNo);
}
}
public List <TripRecord> GetTrips(string order, string sort, string search)
{
string strSQL;
string strOrder = order;
string strSort = sort;
if (String.IsNullOrWhiteSpace(order))
{
strOrder = "userID";
}
if (String.IsNullOrWhiteSpace(sort) || sort != "asc" || sort != "desc")
{
strSort = "asc";
}
try
{
if (m_oleConnection.State == ConnectionState.Closed)
{
m_oleConnection.Open();
}
if (String.IsNullOrWhiteSpace(search))
{
strSQL = "select userID, cityNo, arriveTime, memo, name, ID from Trip where true order by " + strOrder + " " + strSort;
}
else
{
strSQL = "select userID, cityNo, arriveTime, memo, name, ID from Trip where " + search + " order by " + strOrder + " " + strSort;
}
m_oleCommand.Connection = m_oleConnection;
m_oleCommand.CommandText = strSQL;
MySqlDataReader oleReader = m_oleCommand.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(oleReader);
oleReader.Close();
List <TripRecord> trips = null;
if (dt.Rows.Count > 0)
{
trips = new List <TripRecord>();
foreach (DataRow dr in dt.Rows)
{
TripRecord trip = new TripRecord();
trip.userID = dr[0].ToString();
trip.cityNo = dr[1].ToString();
trip.arriveTime = Convert.ToDateTime(dr[2].ToString());
//if (string.IsNullOrWhiteSpace(dr[3].ToString())) trip.memo = ；
trip.memo = dr[3].ToString();
trip.name = dr[4].ToString();
trip.ID = dr[5].ToString();
trips.Add(trip);
}
}
return(trips);
}
catch (InvalidOperationException ex)
{
throw new Common.UserDBException(ex.Message + " " + "Failed to get trips " + search);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw new Common.UserDBException(ex.Message + " " + "Failed to get trips: " + search);
}
catch
{
throw new Common.UserDBException("Failed to get trips: " + search);
}
}
private static TripRecordFactory CsvToBinaryFileWithLookupTable <T>(string[] sources, string destination, char delimiter)
where T : TripRecordModel, new()
{
BinaryWriter <TripRecord> writer = new BinaryWriter <TripRecord>(destination);
TripRecordFactory context = new TripRecordFactory(destination);
T model = new T();
foreach (var source in sources)
{
Console.WriteLine(">Processing {0}<", source);
using (StreamReader sreader = new StreamReader(source))
{
TripRecord record = new TripRecord();
int rowcount = 0;
int currentrow = 0;
//Count Rows
while (!sreader.EndOfStream)
{
sreader.ReadLine();
rowcount++;
}
Console.WriteLine("Row Count = {0}", rowcount);
Timer timer = new Timer(1000);
var action = new ElapsedEventHandler((s, e) =>
{
Console.CursorLeft = 0;
Console.Write("Proccesed {0}/{1} Rows - {2:P2}\t", currentrow, rowcount, (float)currentrow / (float)rowcount);
});
timer.Elapsed += action;
timer.Start();
Dictionary <string, string> row = new Dictionary <string, string>();
sreader.BaseStream.Position = 0; // Reset stream
string[] header = sreader.ReadLine().ToLower().Split(delimiter);
while (!sreader.EndOfStream)
{
row.Clear();
// Read entire row as string and tokenize
string[] tokens = sreader.ReadLine().Split(delimiter).Take(header.Length).ToArray();
for (int i = 0; i < tokens.Length; i++)
{
row.Add(header[i], tokens[i]);
}
//Parse tokens
record = model.ParseTokens(row);
//Write data structure to file
writer.Write(record);
//Index the data structure
DateTime key = new DateTime(record.TimeStamp.Year, record.TimeStamp.Month, record.TimeStamp.Day);
List <long> ret;
if (!context.LookupTable.TryGetValue(key, out ret))
{
ret = new List <long>();
context.LookupTable[key] = ret;
}
ret.Add(record.ID);
currentrow++;
}
action.Invoke(null, null);
Console.WriteLine();
timer.Stop();
timer.Close();
}
writer.Flush();
}
writer.Close();
//Save Lookup table to file
string path = Path.GetDirectoryName(destination);
string name = Path.GetFileNameWithoutExtension(destination);
string filename = Path.Combine(path, string.Format("{0}.{1}", name, Constants.LookupTableExtension));
Console.WriteLine(">Storing Lookup Table<");
BinaryWriter <LookupRow> stream = new BinaryWriter <LookupRow>(filename);
foreach (var entry in context.LookupTable)
{
foreach (var value in entry.Value)
{
LookupRow row = new LookupRow(entry.Key, value);
stream.Write(row);
}
stream.Flush();
}
stream.Close();
Console.WriteLine("Lookup Table Successfully stored at {0}", filename);
return(context);
}
private static void CsvToBinaryFile <T>(string[] sources, string destination, char delimiter, bool isFirstLineHeader)
where T : TripRecordModel, new()
{
BinaryWriter <TripRecord> writer = new BinaryWriter <TripRecord>(destination);
T model = new T();
foreach (var source in sources)
{
Console.WriteLine(">Processing {0}<", source);
using (StreamReader sreader = new StreamReader(source))
{
TripRecord record = new TripRecord();
int rowcount = 0;
int currentrow = 0;
// Remove first line
if (isFirstLineHeader)
{
sreader.ReadLine();
}
while (!sreader.EndOfStream)
{
sreader.ReadLine();
rowcount++;
}
Console.WriteLine("Row Count = {0}", rowcount);
Timer timer = new Timer(1000);
var action = new ElapsedEventHandler((s, e) =>
{
Console.CursorLeft = 0;
Console.Write("Proccesed {0}/{1} Rows - {2:P2}\t", currentrow, rowcount, (float)currentrow / (float)rowcount);
});
timer.Elapsed += action;
timer.Start();
Dictionary <string, string> row = new Dictionary <string, string>();
sreader.BaseStream.Position = 0; // Reset stream
string[] header = sreader.ReadLine().Split(delimiter);
while (!sreader.EndOfStream)
{
// Read entire row as string and tokenize
string[] tokens = sreader.ReadLine().Split(delimiter);
Debug.Assert(row.Count != tokens.Length);
for (int i = 0; i < tokens.Length; i++)
{
row.Add(header[i], tokens[i]);
}
//Parse tokens
record = model.ParseTokens(row);
writer.Write(record);
currentrow++;
}
action.Invoke(null, null);
timer.Stop();
timer.Close();
}
writer.Flush();
}
writer.Close();
}
