public static void InsertAcc(List <string> insertFileList, InsertConfig config, List <InsertDatum> insertDatumList)
{
foreach (var filePath in insertFileList)
//Parallel.For(0, insertFileList.Count, i =>
{
string[] word = filePath.Split('\\');
// ACCファイルでない場合はcontinue
if (System.Text.RegularExpressions.Regex.IsMatch(word[word.Length - 1], @"\d{14}Unsent16HzAccel.csv"))
{
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);
InsertAccRaw(filePath, datum);
}
//});
}
}
public T Insert <T>(InsertConfig config, T insertObject)
{
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
CommandParameter[] parameters = BuildCommandParameters(insertObject, properties);
DbCommand dbCommand = BuildInsertCommand(config, parameters);
int insertCount = 0;
if (dbTransaction == null)
{
try
{
insertCount = db.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
LogHelper.Warn("BlueFramework.Blood.DataAccess.Command.Insert<T> :", ex);
insertCount = 0;
}
}
else
{
insertCount = db.ExecuteNonQuery(dbCommand, dbTransaction);
}
if (insertCount > 0 && !string.IsNullOrEmpty(config.KeyProperty))
{
PropertyInfo propertyInfo = properties.FirstOrDefault(o => o.Name == config.KeyProperty);
propertyInfo.SetValue(insertObject, dbCommand.Parameters[0].Value);
}
return(insertCount > 0 ? insertObject : default(T));
}
public static List <string> DirectorySearch(InsertConfig config)
{
var insertFileList = new List <string>();
foreach (var driver in config.CheckeDrivers)
{
if (driver.Equals(DriverNames.Tommy))
{
CheckFiles(DirectoryNames.DirectoryTommy, config.StartDate, config.EndDate, insertFileList);
}
if (driver.Equals(DriverNames.Mori))
{
CheckFiles(DirectoryNames.DirectoryMori, config.StartDate, config.EndDate, insertFileList);
}
if (driver.Equals(DriverNames.Tamura))
{
CheckFiles(DirectoryNames.DirectoryTamura, config.StartDate, config.EndDate, insertFileList);
}
if (driver.Equals(DriverNames.Uemura))
{
CheckFiles(DirectoryNames.DirectoryUemura, config.StartDate, config.EndDate, insertFileList);
}
if (driver.Equals(DriverNames.Kichise))
{
CheckFiles(DirectoryNames.DirectoryKisse, config.StartDate, config.EndDate, insertFileList);
}
// TODO 研究室メンバー
}
return(insertFileList);
}
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));
}
public void InsertTest()
{
UserInfo userInfo = new UserInfo()
{
UserName = "******",
Birthday = DateTime.Now
};
InsertConfig config = new InsertConfig
{
Id = "test.insertUser",
Sql = "insert into [user](username,birthday,address) values(#{UserName},#{Birthday},#{Address})",
KeyProperty = "UserId",
KeyMadeOrder = IdentityMadeOrder.Inserting,
KeyPropertySql = "select @UserId=@@IDENTITY"
};
Command command = new Command();
UserInfo o = command.Insert <UserInfo>(config, userInfo);
Assert.IsNotNull(o);
}
private DbCommand BuildInsertCommand(InsertConfig config, params CommandParameter[] parameters)
{
string sql = FormatSql(config, parameters);
if (config.KeyMadeOrder == IdentityMadeOrder.Inserting)
{
sql = sql + " " + config.KeyPropertySql;
}
DbCommand dbCommand = db.GetSqlStringCommand(sql);
if (config.KeyMadeOrder == IdentityMadeOrder.Inserting)
{
foreach (CommandParameter parameter in parameters)
{
if (parameter.ParameterName == config.KeyProperty)
{
db.AddOutParameter(
dbCommand,
parameter.ParameterName,
GetDbType(parameter.ParameterType),
32
);
break;
}
}
}
foreach (CommandParameter parameter in parameters)
{
if (parameter.ParameterName != config.KeyProperty)
{
db.AddInParameter(
dbCommand,
parameter.ParameterName,
GetDbType(parameter.ParameterType),
parameter.ParameterValue
);
}
}
return(dbCommand);
}
public static void InsertCorrectedAcc(InsertDatum datum, InsertConfig config)
{
Console.WriteLine("CALLED: InsertCorrectedAcc, " + datum);
var tripsTable = new DataTable();
//if (config.Correction == InsertConfig.GpsCorrection.SpeedLPFMapMatching)
//{
// tripsTable = TripsSpeedLPF005MMDao.Get(datum);
//}
//else
//{
// tripsTable = TripsDao.Get(datum);
//}
foreach (DataRow row in tripsTable.Rows)
{
var correctedAccTable = AccCorrector.CorrectAcc(datum.StartTime, datum.EndTime, datum, row);
// Tripsテーブルの1行ごろの処理なので主キー違反があっても挿入されないだけ
// Trip単位で途中で挿入が異常終了した場合は、DELETEが必要
CorrectedAccDao.Insert(correctedAccTable);
}
}
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);
}
public void TransactionTest()
{
InsertConfig config = new InsertConfig
{
Id = "test.insertUser",
Sql = "insert into [user](username,birthday,address) values(#{UserName},#{Birthday},#{Address})",
KeyProperty = "UserId",
KeyMadeOrder = IdentityMadeOrder.Inserting,
KeyPropertySql = "select @UserId=@@IDENTITY"
};
UpdateConfig updateConfig = new UpdateConfig
{
Id = "test.insertUser",
Sql = "update [user] set username=#{UserName} where id=#{UserId}",
};
DeleteConfig deleteConfig = new DeleteConfig
{
Id = "test.deleteUser",
Sql = "delete from [user] where id=#{value}",
};
int insertCount = 0;
int beginId = 0;
using (var command = new Command())
{
try
{
command.BeginTransaction();
for (int i = 0; i < 10; i++)
{
UserInfo userInfo = new UserInfo()
{
UserId = 0,
UserName = "******" + i.ToString(),
Birthday = DateTime.Now
};
UserInfo o = command.Insert <UserInfo>(config, userInfo);
insertCount++;
if (o != null && i == 0)
{
beginId = o.UserId;
}
}
for (int i = 0; i < 5; i++)
{
UserInfo userInfo = new UserInfo()
{
UserId = i + 50,
UserName = "******" + i.ToString(),
Birthday = DateTime.Now
};
command.Update <UserInfo>(updateConfig, userInfo);
}
for (int i = beginId; i <= beginId + 5; i++)
{
command.Delete(deleteConfig, i);
}
command.CommitTransaction();
}
catch (Exception ex)
{
command.RollbackTransaction();
}
}
Assert.AreEqual(insertCount, 10);
}
