public static void Insert(string query)
{
using (SqlConnection sqlConnection = new SqlConnection(DatabaseAccesser.ConnectionString))
{
try
{
sqlConnection.Open();
SqlCommand command = new SqlCommand(query, sqlConnection);
command.CommandTimeout = 600;
command.ExecuteNonQuery();
}
catch (SqlException sqlException)
{
if (sqlException.Number != NumberOfViolationOfPrimaryKey)
{
Console.WriteLine($"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
LogWritter.WriteLog(LogWritter.LogMode.Error, $"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
}
}
finally
{
sqlConnection.Close();
}
}
}
public static DataTable GetResult(string query)
{
var dataTable = new DataTable();
using (SqlConnection sqlConnection = new SqlConnection(DatabaseAccesser.ConnectionString))
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(query, DatabaseAccesser.ConnectionString);
try
{
sqlConnection.Open();
SqlCommand command = new SqlCommand(query, sqlConnection);
command.CommandTimeout = 600;
sqlDataAdapter.SelectCommand = command;
sqlDataAdapter.Fill(dataTable);
}
catch (SqlException sqlException)
{
// Console.WriteLine($"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
LogWritter.WriteLog(LogWritter.LogMode.Error, $"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
}
finally
{
sqlConnection.Close();
}
}
return(dataTable);
}
public static void InsertEcologDopplerNotMM(InsertDatum datum, MainWindowViewModel.UpdateTextDelegate updateTextDelegate, InsertConfig.GpsCorrection correction)
{
var tripsTable = TripsDopplerNotMMDao.Get(datum);
//int i = 1;
//foreach (DataRow row in tripsTable.Rows)
//{
// updateTextDelegate($"Insetring ECOLOG ... , {i} / {tripsTable.Rows.Count}");
// LogWritter.WriteLog(LogWritter.LogMode.Ecolog, $"Insetring ECOLOG... , { i} / { tripsTable.Rows.Count}, Datum: {datum}");
// var ecologTable = HagimotoEcologCalculator.CalcEcolog(row, datum, correction);
// EcologDao.Insert(ecologTable);
// i++;
//}
Parallel.For(0, tripsTable.Rows.Count, i =>
{
if (tripsTable.Rows[i][(TripsDopplerNotMMDao.ColumnConsumedEnergy)] == DBNull.Value)
{
updateTextDelegate($"Insetring ECOLOGDopplerNotMM ... , {i + 1} / {tripsTable.Rows.Count}");
LogWritter.WriteLog(LogWritter.LogMode.Ecolog, $"Insetring ECOLOGDopplerNotMM... , { i} / { tripsTable.Rows.Count}, Datum: {datum}");
var ecologTable = HagimotoEcologCalculator.CalcEcologDoppler(tripsTable.Rows[i], datum, correction);
EcologDopplerNotMMDao.Insert(ecologTable);
}
});
TripsDopplerNotMMDao.UpdateConsumedEnergy();
}
public static Task InsertGps(List <string> insertFileList, InsertConfig config, int correctionIndex, List <InsertDatum> insertDatumList)
{
var tasks = new List <Task>();
foreach (string filePath in insertFileList)
{
Console.WriteLine("GPSinserting:" + filePath);
string[] word = filePath.Split('\\');
// GPSファイルでない場合はcontinue
if (!System.Text.RegularExpressions.Regex.IsMatch(word[word.Length - 1], @"\d{14}UnsentGPS.csv"))
{
continue;
}
var datum = new InsertDatum()
{
DriverId = DriverNames.GetDriverId(word[DriverIndex]),
CarId = CarNames.GetCarId(word[CarIndex]),
SensorId = SensorNames.GetSensorId(word[SensorIndex]),
StartTime = config.StartDate,
EndTime = config.EndDate,
EstimatedCarModel = EstimatedCarModel.GetModel(config.CarModel)
};
InsertDatum.AddDatumToList(insertDatumList, datum);
LogWritter.WriteLog(LogWritter.LogMode.Gps, $"インサートデータ, FilePath: {filePath}, DriverId: {datum.DriverId}, CarId: {datum.CarId}, SensorId: {datum.SensorId}");
// ファイルごとの処理なので主キー違反があっても挿入されないだけ
var gpsRawTable = InsertGpsRaw(filePath, datum, config.Correction[correctionIndex]);
if (config.Correction[correctionIndex] == InsertConfig.GpsCorrection.SpeedLPFMapMatching ||
config.Correction[correctionIndex] == InsertConfig.GpsCorrection.MapMatching)
{
gpsRawTable = MapMatching.getResultMapMatching(gpsRawTable, datum);
}
else if (config.Correction[correctionIndex] == InsertConfig.GpsCorrection.DopplerSpeed)
{
gpsRawTable = MapMatching.getResultMapMatchingDoppler(gpsRawTable, datum);
}
if (gpsRawTable.Rows.Count != 0)
{
var task = Task.Run(() =>
{
InsertCorrectedGps(gpsRawTable, config.Correction[correctionIndex]);
});
tasks.Add(task);
TripInserter.InsertTripRaw(gpsRawTable, config.Correction[correctionIndex]);
//TripInserter.InsertTrip(datum, config.Correction[correctionIndex]);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Gps, $"ファイルの行数が0行のためインサートを行いませんでした: {filePath}");
}
}
return(Task.WhenAll(tasks));
}
private void Application_DispatcherUnhandledException(
object sender,
DispatcherUnhandledExceptionEventArgs e)
{
LogWritter.WriteLog(LogWritter.LogMode.Error, e.Exception.Message + ", " + e.Exception.StackTrace);
e.Handled = true;
}
public ActionResult Login(Login model)
{
if (ModelState.IsValid)
{
var user = db.Users.Where(u => String.Compare(u.UserName, model.UserName) == 0).FirstOrDefault();
if (user != null)
{
if (user.IsActive)
{
// Verify user password
var success = SaltedHash.Verify(user.Salt, user.Password, model.Password);
if (success)
{
// Save authentication info
ElectricalShopPrincipleModel principle = new ElectricalShopPrincipleModel();
principle.UserId = user.UserId;
principle.FullName = user.FullName;
principle.Roles = user.Roles.Select(r => r.RoleName).ToArray();
// Add authentication cookie
FormsAuthenticationTicket authTicket = new FormsAuthenticationTicket(1, model.UserName,
DateTime.Now, DateTime.Now.AddDays(7), model.RememberMe, JsonConvert.SerializeObject(principle));
String authTicketEncrypted = FormsAuthentication.Encrypt(authTicket);
HttpCookie asCookie = new HttpCookie(FormsAuthentication.FormsCookieName, authTicketEncrypted);
Response.Cookies.Add(asCookie);
// Write action log
Log log = new Log();
log.LogDate = DateTime.Now;
log.Action = "Login";
log.Tags = GetRequestedIP() + "," + model.UserName;
log.Message = "Đăng nhập hệ thống";
LogWritter.WriteLog(log);
return(RedirectToAction("Index", "Admin"));
}
else
{
ModelState.AddModelError("", "Sai mật khẩu!");
}
}
else
{
ModelState.AddModelError("", "Tài khoản đã bị khóa!");
}
}
else
{
ModelState.AddModelError("", "Tài khoản không tồn tại trong hệ thống!");
}
}
return(View(model));
}
public ActionResult Index()
{
// Write action log
Log log = new Log();
log.LogDate = DateTime.Now;
log.Action = "View Roles";
log.Tags = GetRequestedIP() + "," + GetLogonUserName();
log.Message = "Xem danh sách chức danh";
LogWritter.WriteLog(log);
return(View());
}
public ActionResult Profiler(Profiler model)
{
if (ModelState.IsValid)
{
var user = db.Users.Where(u => u.UserId == model.UserId).FirstOrDefault();
if (user != null)
{
if (String.IsNullOrWhiteSpace(model.NewPassword))
{
user.FullName = model.FullName;
user.Phone = model.Phone;
user.Email = model.Email;
db.SaveChanges();
RedirectToAction("Index", "Admin");
}
else
{
/* User changed password */
if (SaltedHash.Verify(user.Salt, user.Password, model.Password))
{
SaltedHash sh = new SaltedHash(model.NewPassword);
user.Password = sh.Hash;
user.Salt = sh.Salt;
user.FullName = model.FullName;
user.Phone = model.Phone;
user.Email = model.Email;
db.SaveChanges();
// Write action log
Log log = new Log();
log.LogDate = DateTime.Now;
log.Action = "Update profile";
log.Tags = GetRequestedIP() + "," + model.UserName;
log.Message = "Cập nhật thông tin cá nhân";
LogWritter.WriteLog(log);
RedirectToAction("Index", "Admin");
}
else
{
ModelState.AddModelError("", "Sai mật khẩu!");
}
}
}
else
{
return(RedirectToAction("Login"));
}
}
return(View(model));
}
public ActionResult SignOut()
{
// Write action log
Log log = new Log();
log.LogDate = DateTime.Now;
log.Action = "SignOut";
log.Tags = GetRequestedIP() + "," + GetLogonUserName();
log.Message = "Đăng xuất hệ thống";
LogWritter.WriteLog(log);
FormsAuthentication.SignOut();
return(RedirectToAction("Login"));
}
public static void Insert(String tableName, DataTable dataTable)
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DatabaseAccesser.ConnectionString))
{
try
{
bulkCopy.BulkCopyTimeout = 600;
bulkCopy.DestinationTableName = tableName;
bulkCopy.WriteToServer(dataTable);
}
catch (SqlException sqlException)
{
if (sqlException.Number != NumberOfViolationOfPrimaryKey)
{
Console.WriteLine($"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
LogWritter.WriteLog(LogWritter.LogMode.Error, $"ERROR: {sqlException.Message}, {sqlException.StackTrace}");
}
}
finally
{
bulkCopy.Close();
}
}
}
public static void InsertTrip(InsertDatum datum, InsertConfig.GpsCorrection correction, bool isCheckedSightseeingInsert)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"TRIP挿入開始, DRIVER_ID: {datum.DriverId}, CAR_ID: {datum.CarId}, SENSOR_ID: {datum.SensorId}");
var tripsRawTable = new DataTable();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
tripsRawTable = TripsRawSpeedLPF005MMDao.Get(datum);
TripsSpeedLPF005MMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
tripsRawTable = TripsRawMMDao.Get(datum);
TripsMMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.Normal)
{
tripsRawTable = TripsRawDao.Get(datum);
TripsDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed)
{
tripsRawTable = TripsRawDopplerDao.Get(datum);
TripsDopplerDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
tripsRawTable = TripsRawDopplerNotMMDao.Get(datum);
TripsDopplerNotMMDao.DeleteTrips();
}
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"挿入対象のRAWデータ: {tripsRawTable.Rows.Count}");
for (int i = 0; i < tripsRawTable.Rows.Count; i++)
{
DataTable tripsTable = DataTableUtil.GetTripsTable();
// 観光オプションによるインサート処理はあらかじめ切り分ける。
if (isCheckedSightseeingInsert)
{
InsertSightSeeingTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// 自宅出発
else if (IsHome(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum))
{
InsertOutwardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// YNU出発
else if (IsYnu(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
InsertHomewardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// 1トリップごとなので主キー違反があっても挿入されないだけ
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
TripsSpeedLPF005MMDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
TripsMMDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.Normal)
{
TripsDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed)
{
if (tripsTable.Rows.Count != 0)
{
var gpsCorrectedTable = CorrectedGPSDopplerDao.GetNormalized(
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnStartTime),
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnEndTime),
datum);
if (gpsCorrectedTable.Rows.Count != 0)
{
TripsDopplerDao.Insert(tripsTable);
}
}
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM)
{
if (tripsTable.Rows.Count != 0)
{
var gpsCorrectedTable = CorrectedGpsDopplerNotMMDao.GetNormalized(
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnStartTime),
tripsTable.Rows[0].Field <DateTime>(TripsDopplerDao.ColumnEndTime),
datum);
if (gpsCorrectedTable.Rows.Count != 0)
{
TripsDopplerNotMMDao.Insert(tripsTable);
}
}
}
}
}
/*
* InsertSightSeeingTrip()
* 観光用トリップを挿入するためのメソッド
* YNU出発と観光地出発の2種類があることに注意が必要になる。
*/
private static void InsertSightSeeingTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int startIndex, InsertConfig.GpsCorrection correction)
{
// InsertHomewardTripと同じような処理を記述
// tripのスタートのtripsRawのインデックスをstartIndex
// 現在注目しているtripsRawのインデックスをcurrentIndex
int currentIndex = startIndex;
// tripChangeFlagはループを抜けるためのフラグ
// このフラグが立つと、異なるトリップに到達したことを示す
bool tripChangeFlag = false;
// TripsRawを結合してTripsを生成する。
while (currentIndex < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// スタートがynuか観光地 かつ ゴールがynuか観光地
// でもynuからynuのトリップは考えない
// スタート,ゴールがynuであるか
bool isStartYnu = IsYnu(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
bool isEndYnu = IsYnu(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
// スタートゴールが観光地であるか
bool isStartSightseeingSpot = IsSightseeing(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
bool isEndSightseeingSpot = IsSightseeing(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
// スタートゴールが自宅であるか
bool isStartHome = IsHome(tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum);
bool isEndHome = IsHome(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum);
// YNU to YNUは排除
if (isStartYnu && isEndYnu)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒YNUトリップなので挿入しません。"
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
tripChangeFlag = true;
}
// 自宅 to 自宅 も排除
else if (isStartHome && isEndHome)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "Home⇒Homeトリップなので挿入しません。"
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
tripChangeFlag = true;
}
// 候補地 to 候補地のトリップを挿入
else if ((isStartYnu || isStartSightseeingSpot || isStartHome) &&
(isEndYnu || isEndSightseeingSpot || isEndHome))
{
var row = tripsTable.NewRow();
row.SetField(TripsDao.ColumnTripId, GetMaxTripId(correction));
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[startIndex].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[startIndex].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "tourism");
TimeSpan span = tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[startIndex].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching &&
!TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching &&
!TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal &&
!TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed &&
!TripsDopplerDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM && !TripsDopplerNotMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex],
tripsRawTable.Rows[currentIndex]));
}
tripChangeFlag = true;
}
currentIndex++;
if (currentIndex == tripsRawTable.Rows.Count || tripChangeFlag)
{
break;
}
// YNUにも観光地にも自宅にも到着しないまま、開始地点がYNUか観光地か自宅になった場合
if (IsYnu(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude)) ||
IsSightseeing(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude)) ||
IsHome(tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[currentIndex].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[currentIndex].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum)
)
{
tripChangeFlag = true;
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNUor自宅or観光地⇒？トリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[startIndex], tripsRawTable.Rows[currentIndex]));
}
}
}
//TRIP_DIRECTION = 'other' のインサート
private static void InsertOtherTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int i, InsertConfig.GpsCorrection correction)
{
int j = i;
bool tripChangeFlag = false;
// TripsRaw を結合して Trips を生成するループ
while (j < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// その他登録地点 ⇒ その他登録地点
if (IsOther(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
// その他登録地点 ⇒ YNU
if (IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
//その他登録地点 ⇒ 自宅
else if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum))
{
var row = tripsTable.NewRow();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsSpeedLPF005MMDao.GetMaxTripId() + 1);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
row.SetField(TripsDao.ColumnTripId, TripsMMDao.GetMaxTripId() + 1);
}
else
{
row.SetField(TripsDao.ColumnTripId, TripsDao.GetMaxTripId() + 1);
}
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "other");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
j++;
}
}
public static void InsertTrip(InsertDatum datum, InsertConfig.GpsCorrection correction)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"TRIP挿入開始, DRIVER_ID: {datum.DriverId}, CAR_ID: {datum.CarId}, SENSOR_ID: {datum.SensorId}");
var tripsRawTable = new DataTable();
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
tripsRawTable = TripsRawSpeedLPF005MMDao.Get(datum);
TripsSpeedLPF005MMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
tripsRawTable = TripsRawMMDao.Get(datum);
TripsMMDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
else
{
tripsRawTable = TripsRawDao.Get(datum);
TripsDao.DeleteTrips(); //途中中断された際に作成したトリップを削除
}
LogWritter.WriteLog(LogWritter.LogMode.Trip, $"挿入対象のRAWデータ: {tripsRawTable.Rows.Count}");
for (int i = 0; i < tripsRawTable.Rows.Count; i++)
{
DataTable tripsTable = DataTableUtil.GetTripsTable();
// 自宅出発
if (IsHome(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime),
datum))
{
InsertOutwardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// YNU出発
else if (IsYnu(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
InsertHomewardTrip(tripsRawTable, tripsTable, datum, i, correction);
}
//自宅・YNU以外出発
else if (IsOther(tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
InsertOtherTrip(tripsRawTable, tripsTable, datum, i, correction);
}
// 1トリップごとなので主キー違反があっても挿入されないだけ
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
{
TripsSpeedLPF005MMDao.Insert(tripsTable);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching)
{
TripsMMDao.Insert(tripsTable);
}
else
{
TripsDao.Insert(tripsTable);
}
}
}
public async void Insert()
{
this.InsertDatumList = new List <InsertDatum>();
this.InsertFileList = new List <string>();
IsEnabledInsertButton = false;
IsEnabledStartUpLoopButton = false;
System.Diagnostics.Stopwatch sw = new System.Diagnostics.Stopwatch();
this.InsertConfig = this.GenerateInsertConfig();
#region ファイル検索
this.LogText += LogTexts.DuringCheckOfTheUpdateFile + "\n";
LogWritter.WriteLog(LogWritter.LogMode.Search, LogTexts.DuringCheckOfTheUpdateFile + "\n");
await Task.Run(() =>
{
this.InsertFileList = DirectorySearcher.DirectorySearch(this.InsertConfig);
});
this.LogText += $"{LogTexts.NumberOfTheInsertedFile}: {this.InsertFileList.Count}\n";
LogWritter.WriteLog(LogWritter.LogMode.Search, $"{LogTexts.NumberOfTheInsertedFile}: {this.InsertFileList.Count}\n");
#endregion
#region GPS挿入
this.LogText += LogTexts.TheSrartOfTheInsertingGps + "\n";
LogWritter.WriteLog(LogWritter.LogMode.Gps, LogTexts.TheSrartOfTheInsertingGps + "\n");
//await Task.Run(() =>
//{
// for (int i = 0; i < this.InsertConfig.Correction.Count; i++)
// {
// GpsInserter.InsertGps(this.InsertFileList, this.InsertConfig, i, this.InsertDatumList);
// }
//});
Parallel.For(0, this.InsertConfig.Correction.Count, i =>
{
GpsInserter.InsertGps(this.InsertFileList, this.InsertConfig, i, this.InsertDatumList);
});
this.LogText += LogTexts.TheEndOfTheInsertingGps + "\n";
LogWritter.WriteLog(LogWritter.LogMode.Gps, LogTexts.TheEndOfTheInsertingGps + "\n");
#endregion
#region 加速度挿入
if (IsCheckedInsertAcc)
{
this.LogText += LogTexts.TheSrartOfTheInsertingAcc + "\n";
LogWritter.WriteLog(LogWritter.LogMode.Acc, LogTexts.TheSrartOfTheInsertingAcc + "\n");
await Task.Run(() =>
{
AccInserter.InsertAcc(this.InsertFileList, this.InsertConfig, this.InsertDatumList);
});
this.LogText += LogTexts.TheEndOfTheInsertingAcc + "\n";
LogWritter.WriteLog(LogWritter.LogMode.Acc, LogTexts.TheEndOfTheInsertingAcc + "\n");
}
#endregion
foreach (var datum in InsertDatumList)
{
#region トリップ挿入
//await Task.Run(() =>
//{
for (int i = 0; i < this.InsertConfig.Correction.Count; i++)
{
TripInserter.InsertTrip(datum, InsertConfig.Correction[i]);
}
//});
#endregion
#region 補正加速度挿入
//if (IsCheckedInsertCorrectedAcc)
//{
// await Task.Run(() =>
// {
// AccInserter.InsertCorrectedAcc(datum, InsertConfig);
// });
//}
#endregion
}
int count = 0;
Parallel.For(0, InsertDatumList.Count, i =>
{
#region ECOLOG挿入
// sw.Start();
if (IsCheckedSpeedLPFMapMatching)
{
EcologInserter.InsertEcologSpeedLPF005MM(InsertDatumList[i], this.UpdateText, InsertConfig.GpsCorrection.SpeedLPFMapMatching);
}
if (IsCheckedMapMatching)
{
EcologInserter.InsertEcologMM(InsertDatumList[i], this.UpdateText, InsertConfig.GpsCorrection.MapMatching);
}
if (IsCheckedNormal)
{
EcologInserter.InsertEcolog(InsertDatumList[i], this.UpdateText, InsertConfig.GpsCorrection.Normal, out count);
}
// sw.Stop();
// LogWritter.WriteLog(LogWritter.LogMode.Elapsedtime, "Total Time:" + sw.Elapsed);
#endregion
});
this.LogText += LogTexts.TheEndOfTheInsertingEcolog + "\n";
/*if (count > 0)
* {
* SlackUtil.commentToSlack(InsertConfig.StartDate, InsertConfig.EndDate, InsertConfig.Correction);
* }
* else {
* SlackUtil.commentToSlackNotInsert(InsertConfig.StartDate, InsertConfig.EndDate, InsertConfig.Correction);
* }*/
IsEnabledInsertButton = true;
IsEnabledStartUpLoopButton = true;
}
private InsertConfig GenerateInsertConfig()
{
var insertConfig = InsertConfig.GetInstance();
#region ドライバーの設定
if (this.IsCheckedTommy)
{
insertConfig.CheckeDrivers.Add(DriverNames.Tommy);
}
if (this.IsCheckedMori)
{
insertConfig.CheckeDrivers.Add(DriverNames.Mori);
}
if (this.IsCheckedTamura)
{
insertConfig.CheckeDrivers.Add(DriverNames.Tamura);
}
if (this.IsCheckedLabMember)
{
insertConfig.CheckeDrivers.Add(DriverNames.Arisimu);
}
//if (this.IsCheckedLabMember)
//insertConfig.CheckeDrivers.Add(DriverNames.Uemura);
// TODO 研究室メンバー
#endregion
#region 期間の設定
if (IsCheckedPeriod)
{
insertConfig.StartDate = this.StartDate;
insertConfig.EndDate = this.EndDate;
}
else
{
insertConfig.StartDate = DateTime.Now.AddDays(-7);
insertConfig.EndDate = DateTime.Now.AddDays(1);
}
#endregion
#region 推定対象車両の設定
if (this.IsCheckedLeafEarlyModel)
{
insertConfig.CarModel = InsertConfig.EstimatedCarModel.LeafEarlyModel;
}
#endregion
#region 推定モデルの設定
if (this.IsCheckedEvModel)
{
insertConfig.EstModel = InsertConfig.EstimationModel.EvEnergyConsumptionModel;
}
else if (this.IsCheckedMlModel)
{
insertConfig.EstModel = InsertConfig.EstimationModel.MachineLearningModel;
}
#endregion
#region GPS補正の設定
if (this.IsCheckedNormal)
{
insertConfig.Correction.Add(InsertConfig.GpsCorrection.Normal);
}
if (this.IsCheckedMapMatching)
{
insertConfig.Correction.Add(InsertConfig.GpsCorrection.MapMatching);
}
//else if (this.IsCheckedDeadReckoning)
// insertConfig.Correction = InsertConfig.GpsCorrection.DeadReckoning;
if (this.IsCheckedSpeedLPFMapMatching)
{
insertConfig.Correction.Add(InsertConfig.GpsCorrection.SpeedLPFMapMatching);
}
#endregion
LogWritter.WriteLog(LogWritter.LogMode.Search, insertConfig.ToString());
return(insertConfig);
}
private static void InsertHomewardTrip(DataTable tripsRawTable, DataTable tripsTable, InsertDatum datum, int i, InsertConfig.GpsCorrection correction)
{
int j = i;
bool tripChangeFlag = false;
// TripsRaw を結合して Trips を生成するループ
while (j < tripsRawTable.Rows.Count && tripChangeFlag == false)
{
// YNU ⇒ 自宅
if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime),
datum))
{
var row = tripsTable.NewRow();
row.SetField(TripsDao.ColumnTripId, GetMaxTripId(correction));
row.SetField(TripsDao.ColumnDriverId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnDriverId));
row.SetField(TripsDao.ColumnCarId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnCarId));
row.SetField(TripsDao.ColumnSensorId, tripsRawTable.Rows[i].Field <int>(TripsRawDao.ColumnSensorId));
row.SetField(TripsDao.ColumnStartTime, tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime));
row.SetField(TripsDao.ColumnEndTime, tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime));
row.SetField(TripsDao.ColumnStartLatitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLatitude));
row.SetField(TripsDao.ColumnStartLongitude, tripsRawTable.Rows[i].Field <double>(TripsRawDao.ColumnStartLongitude));
row.SetField(TripsDao.ColumnEndLatitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude));
row.SetField(TripsDao.ColumnEndLongitude, tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude));
row.SetField(TripsDao.ColumnTripDirection, "homeward");
TimeSpan span = tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnEndTime)
- tripsRawTable.Rows[i].Field <DateTime>(TripsRawDao.ColumnStartTime);
if (span.TotalHours > 12)
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "別々のトリップを結合する可能性があるので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
break;
}
if (correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching && !TripsSpeedLPF005MMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.MapMatching && !TripsMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.Normal && !TripsDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerSpeed && !TripsDopplerDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else if (correction == InsertConfig.GpsCorrection.DopplerNotMM && !TripsDopplerNotMMDao.IsExsistsTrip(row))
{
tripsTable.Rows.Add(row);
}
else
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "既にこのトリップは挿入されているので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
tripChangeFlag = true;
}
// YNU ⇒ YNU
else if (IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnEndLongitude)))
{
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒YNUトリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
// Trip の挿入は行わない
// ループの初期化
tripChangeFlag = true;
}
j++;
// YNU ⇒ ？
if (j < tripsRawTable.Rows.Count && tripChangeFlag != true)
{
// 自宅にも、学校にも到着しないまま、開始地点が自宅か学校になった場合
if (IsHome(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLatitude),
tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLongitude),
tripsRawTable.Rows[j].Field <DateTime>(TripsRawDao.ColumnStartTime), datum) ||
IsYnu(tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLatitude), tripsRawTable.Rows[j].Field <double>(TripsRawDao.ColumnStartLongitude)))
{
tripChangeFlag = true;
LogWritter.WriteLog(LogWritter.LogMode.Trip, "YNU⇒？トリップなので挿入しません "
+ ConvertRowToString(tripsRawTable.Rows[i], tripsRawTable.Rows[j]));
}
}
}
}