public static DataTable GetSelectedData()
{
StringBuilder query = new StringBuilder();
query.AppendLine($"SELECT *");
query.AppendLine($"FROM " + TableName);
/*query.AppendLine($"WHERE link_id = 'RB140900511749'"); //シミュレーションの対象とする地点（サグ地点）の道路リンクID（ここから）
* query.AppendLine($" OR link_id = 'RB140900511750'");
* query.AppendLine($" OR link_id = 'RB140900511751'");
* query.AppendLine($" OR link_id = 'RB140900511752'");
* query.AppendLine($" OR link_id = 'RB140900511753'");
* query.AppendLine($" OR link_id = 'RB140900511754'");
* query.AppendLine($" OR link_id = 'RB140900511756'");
* query.AppendLine($" OR link_id = 'RB140900519680'");
* query.AppendLine($" OR link_id = 'RB140900519662'");
* query.AppendLine($" OR link_id = 'RB140900519666'");
* query.AppendLine($" OR link_id = 'RB140900519692'");
* query.AppendLine($" OR link_id = 'RB140900519711'");
* query.AppendLine($" OR link_id = 'RB140900714459'");
* query.AppendLine($" OR link_id = 'RB140900714494'");
* query.AppendLine($" OR link_id = 'RB140900714538'");
* query.AppendLine($" OR link_id = 'RB140900714273'");//（ここまで）*/
//TODO SQL書き換え
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static DataTable GetAccurateAccBreakingRaw(DateTime startTime, DateTime endTime, InsertDatum datum)
{
string query = "with LOW_SPEED as ";
query += "( ";
query += " select g1.DRIVER_ID,g1.SENSOR_ID,g1.JST ";
query += " from CORRECTED_GPS g1,CORRECTED_GPS g2 ";
query += " where g1.DRIVER_ID = "+ datum.DriverId + " ";
query += " and g1.SENSOR_ID = "+ datum.SensorId + " ";
query += " and g1.SPEED < 10 ";
query += " and g2.DRIVER_ID = "+ datum.DriverId + " ";
query += " and g2.SENSOR_ID = "+ datum.SensorId + " ";
query += " and g2.SPEED > 10 ";
query += " and g1.JST = DATEADD(second,1,g2.JST) ";
query += " and g1.JST > '"+ startTime + "' ";
query += " and g1.JST < '"+ endTime + "' ";
query += ") ";
query += "select * ";
query += "from LOW_SPEED ";
query += "except ";
query += "select LOW_SPEED.DRIVER_ID,LOW_SPEED.SENSOR_ID,CONVERT(varchar,LOW_SPEED.JST,121) as JST ";
query += "from CORRECTED_GPS,LOW_SPEED ";
query += "where CORRECTED_GPS.DRIVER_ID = LOW_SPEED.DRIVER_ID ";
query += "and CORRECTED_GPS.SENSOR_ID = LOW_SPEED.SENSOR_ID ";
query += "and CORRECTED_GPS.JST < LOW_SPEED.JST ";
query += "and CORRECTED_GPS.JST >= DATEADD(second,-10,LOW_SPEED.JST) ";
query += "and SPEED < 10 ";
query += "group by LOW_SPEED.DRIVER_ID,LOW_SPEED.SENSOR_ID,LOW_SPEED.JST ";
return(DatabaseAccesser.GetResult(query));
}
public static DataTable Get(DateTime startPeriod, DateTime endPeriod)
{
StringBuilder query = new StringBuilder();
query.AppendLine($"SELECT *");
query.AppendLine($"FROM " + TableName);
query.AppendLine($"WHERE jst >= '{startPeriod}'");
query.AppendLine($" AND jst <= '{endPeriod}'");
/*query.AppendLine($" AND link_id = 'RB140900511749'");//シミュレーションの対象とする地点（サグ地点）の道路リンクID（ここから）
* query.AppendLine($" OR link_id = 'RB140900511750'");
* query.AppendLine($" OR link_id = 'RB140900511751'");
* query.AppendLine($" OR link_id = 'RB140900511752'");
* query.AppendLine($" OR link_id = 'RB140900511753'");
* query.AppendLine($" OR link_id = 'RB140900511754'");
* query.AppendLine($" OR link_id = 'RB140900511756'");
* query.AppendLine($" OR link_id = 'RB140900519680'");
* query.AppendLine($" OR link_id = 'RB140900519662'");
* query.AppendLine($" OR link_id = 'RB140900519666'");
* query.AppendLine($" OR link_id = 'RB140900519692'");
* query.AppendLine($" OR link_id = 'RB140900519711'");
* query.AppendLine($" OR link_id = 'RB140900714459'");
* query.AppendLine($" OR link_id = 'RB140900714494'");
* query.AppendLine($" OR link_id = 'RB140900714538'");
* query.AppendLine($" OR link_id = 'RB140900714273'");//（ここまで）*/
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static DataTable GetLinkTableforMM(int[] id)
{
string query = "select l1.LINK_ID as LINK_ID , l1.NUM, l1.LATITUDE as START_LAT, l1.LONGITUDE as START_LONG,l2.LATITUDE as END_LAT, l2.LONGITUDE as END_LONG ";
query += ",SQRT((l1.LATITUDE - l2.LATITUDE) * (l1.LATITUDE - l2.LATITUDE) + (l1.LONGITUDE - l2.LONGITUDE) * (l1.LONGITUDE - l2.LONGITUDE)) as DISTANCE ";
query += "from LINKS as l1,LINKS as l2,( ";
query += "select l1.NUM,MIN(l2.NUM - l1.NUM) as diff ";
query += "from LINKS as l1,LINKS as l2,SEMANTIC_LINKS ";
query += "where l1.NUM < l2.NUM ";
query += "and l1.LINK_ID = l2.LINK_ID ";
query += "and l1.LINK_ID = SEMANTIC_LINKS.LINK_ID ";
query += "and SEMANTIC_LINK_ID in ( " + id[0];
for (int i = 1; i < id.Length; i++)
{
query += ", " + id;
}
query += ") ";
query += "group by l1.NUM) as Corres ";
query += "where l1.NUM = Corres.NUM ";
query += "and l2.NUM = l1.NUM + Corres.diff ";
query += "order by NUM ";
return(DatabaseAccesser.GetResult(query));
}
public static AltitudeDatum Get(double latitude, double longitude)
{
string query = "select * ";
query += $"FROM {TableName} ";
query += $"WHERE {ColumnLowerLatitude} <= " + latitude + " ";
query += $"AND {ColumnUpperLatitude} > " + latitude + " ";
query += $"AND {ColumnLowerLongitude} <= " + longitude + " ";
query += $"AND {ColumnUpperLongitude} > " + longitude + " ";
var result = DatabaseAccesser.GetResult(query);
AltitudeDatum resultDatum = new AltitudeDatum();
if (result.Rows.Count > 0)
{
resultDatum = new AltitudeDatum
{
LowerLatitude = result.Rows[0].Field <double?>(ColumnLowerLatitude),
LowerLongitude = result.Rows[0].Field <double?>(ColumnLowerLongitude),
UpperLatitude = result.Rows[0].Field <double?>(ColumnUpperLatitude),
UpperLongitude = result.Rows[0].Field <double?>(ColumnUpperLongitude),
Altitude = result.Rows[0].Field <float?>(ColumnAltitude)
};
}
return(resultDatum);
}
public static DataTable GetNormalized(DateTime startTime, DateTime endTime, InsertDatum datum)
{
//GPRMC車速から参照するように改造済み
var query = new StringBuilder();
query.AppendLine($"WITH convert_gps");
query.AppendLine($"AS (");
query.AppendLine($" SELECT GPS.{ColumnDriverId}");
query.AppendLine($" ,GPS.{ColumnCarId}");
query.AppendLine($" ,GPS.{ColumnSensorId}");
query.AppendLine($" ,CONVERT(DATETIME, CONVERT(VARCHAR(30), CONVERT(DATETIME, (");
query.AppendLine($" CASE ");
query.AppendLine($" WHEN DATEPART(Ms, GPS.{ColumnJst}) >= 500");
query.AppendLine($" THEN DATEADD(SECOND, 1, GPS.{ColumnJst})");
query.AppendLine($" ELSE GPS.{ColumnJst}");
query.AppendLine($" END");
query.AppendLine($" )), 20)) AS {ColumnJst}");
query.AppendLine($" ,GPS.{ColumnLatitude}");
query.AppendLine($" ,GPS.{ColumnLongitude}");
query.AppendLine($" ,CASE ");
query.AppendLine($" WHEN GPS.{ColumnBearing} IS NULL");
query.AppendLine($" THEN RMC.MOVING_SPEED * 1.852");
query.AppendLine($" ELSE GPS.{ColumnSpeed}");
query.AppendLine($" END AS {ColumnSpeed}");
query.AppendLine($" ,{ColumnHeading}");
query.AppendLine($" ,{ColumnDistanceDifference}");
query.AppendLine($" ,{ColumnLinkId}");
query.AppendLine($" ,{ColumnRoadTheta}");
query.AppendLine($" FROM {TableName} AS GPS");
query.AppendLine($" LEFT JOIN GPRMC_RAW AS RMC");
query.AppendLine($" ON GPS.{ColumnSensorId} = RMC.{ColumnSensorId}");
query.AppendLine($" AND GPS.{ColumnJst} = RMC.{ColumnJst}");
query.AppendLine($" WHERE GPS.{ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND GPS.{ColumnCarId} = {datum.CarId}");
query.AppendLine($" AND GPS.{ColumnSensorId} = {datum.SensorId}");
query.AppendLine($" AND GPS.{ColumnJst} >= '{startTime}'");
query.AppendLine($" AND GPS.{ColumnJst} <= '{endTime}'");
query.AppendLine($" )");
query.AppendLine($"SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($" ,AVG({ColumnLatitude}) AS {ColumnLatitude}");
query.AppendLine($" ,AVG({ColumnLongitude}) AS {ColumnLongitude}");
query.AppendLine($" ,CAST(AVG({ColumnSpeed}) AS real) AS {ColumnSpeed}");
query.AppendLine($" ,CAST(AVG({ColumnHeading}) AS real) AS {ColumnHeading}");
query.AppendLine($" ,CAST(SUM({ColumnDistanceDifference}) AS real) AS {ColumnDistanceDifference}");
query.AppendLine($" ,MIN({ColumnLinkId}) AS {ColumnLinkId}");
query.AppendLine($" ,CAST(AVG({ColumnRoadTheta}) AS real) AS {ColumnRoadTheta}");
query.AppendLine($"FROM convert_gps");
query.AppendLine($"WHERE speed IS NOT NULL");
query.AppendLine($"GROUP BY {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($"ORDER BY {ColumnJst}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static int GetMaxMeshId()
{
string query = "SELECT MAX(mesh_id) AS max_id ";
query += $"FROM {TableName}";
return(DatabaseAccesser.GetResult(query).Rows[0].Field <int>("max_id"));
}
public static DataTable Get(string linkId)
{
string query = "SELECT * ";
query += $"FROM {TableName} ";
query += $"WHERE link_id = '{linkId}' ";
return(DatabaseAccesser.GetResult(query));
}
public static DataTable GetAltitude(double latitude, double longitude)
{
string query = "SELECT * FROM " + TableName;
query += " WHERE lower_latitude <= " + latitude + " AND upper_latitude > " + latitude + " AND lower_longitude <= " + longitude;
query += " AND upper_longitude > " + longitude;
return(DatabaseAccesser.GetResult(query));
}
public static DataTable Get(DateTime startPeriod, DateTime endPeriod)
{
StringBuilder query = new StringBuilder();
query.AppendLine($"SELECT *");
query.AppendLine($"FROM " + TableName);
query.AppendLine($"WHERE jst >= '{startPeriod}'");
query.AppendLine($" AND jst <= '{endPeriod}'");
return(DatabaseAccesser.GetResult(query.ToString()));
}
/***
* このレベルになるとDB側にテーブル関数作ったほうがいいよね...
* 頑張れる方、リファクタリングしてください
* Elizabeth
* Your tears and my fears are almost disappear
* So let's share the perfect time,For you and me
* You knocked on my door
* So let's start our journey
* Because you came to see me first
***/
public static DataTable GetAccurateStoppingAccRaw(int timeDiff, InsertDatum datum)
{
string query = "with LOW_SPEED as ";
query += "( ";
query += " select DRIVER_ID,SENSOR_ID,DATEADD(second,-1,JST) as START_TIME, JST as END_TIME ";
query += " from CORRECTED_GPS ";
query += " where DRIVER_ID = "+ datum.DriverId + " ";
query += " and SENSOR_ID = "+ datum.SensorId + " ";
query += " and SPEED < 1 ";
query += " and JST >= '"+ datum.StartTime + "' ";
query += " and JST <= '"+ datum.EndTime + "' ";
query += ") ";
query += ", UPPER_TIME as ";
query += "( ";
query += " select ROW_NUMBER() over(order by START_TIME) as NUMBER, DRIVER_ID, SENSOR_ID, START_TIME ";
query += " from ";
query += " ( ";
query += " select DRIVER_ID,SENSOR_ID,START_TIME ";
query += " from LOW_SPEED ";
query += " except ";
query += " select DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from LOW_SPEED ";
query += " ) as UP ";
query += ") ";
query += ", LOWER_TIME as ";
query += "( ";
query += " select ROW_NUMBER() over(order by END_TIME) as NUMBER, DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from ";
query += " ( ";
query += " select DRIVER_ID,SENSOR_ID,END_TIME ";
query += " from LOW_SPEED ";
query += " except ";
query += " select DRIVER_ID,SENSOR_ID,START_TIME ";
query += " from LOW_SPEED ";
query += " ) as LOW ";
query += ") ";
query += ", LOW_SPEED_SPAN as ";
query += "( ";
query += " select LOWER_TIME.DRIVER_ID,LOWER_TIME.SENSOR_ID,UPPER_TIME.START_TIME,LOWER_TIME.END_TIME ";
query += " from LOWER_TIME,UPPER_TIME ";
query += " where LOWER_TIME.NUMBER = UPPER_TIME.NUMBER ";
query += ") ";
query += "select AVG(ACC_X) as ACC_X,AVG(ACC_Y) as ACC_Y,AVG(ACC_Z) as ACC_Z ";
query += "from ANDROID_ACC_RAW,LOW_SPEED_SPAN ";
query += "where ANDROID_ACC_RAW.DRIVER_ID = LOW_SPEED_SPAN.DRIVER_ID ";
query += "and ANDROID_ACC_RAW.SENSOR_ID = LOW_SPEED_SPAN.SENSOR_ID ";
query += "and ANDROID_ACC_RAW.DATETIME <= DATEADD(MILLISECOND,-1*" + timeDiff + ",LOW_SPEED_SPAN.END_TIME) ";
query += "and ANDROID_ACC_RAW.DATETIME > DATEADD(MILLISECOND,-1*" + timeDiff + ",LOW_SPEED_SPAN.START_TIME) ";
return(DatabaseAccesser.GetResult(query));
}
public static int GetEfficiency(int torque, int rev)
{
var query = new StringBuilder();
query.AppendLine("SELECT * ");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE torque = {torque}");
query.AppendLine($"AND rev = {rev}");
return(DatabaseAccesser.GetResult(query.ToString())
.AsEnumerable()
.Select(v => v.Field <int?>(EfficiencyDao.ColumnEfficiency)).FirstOrDefault() ?? -1);
}
public static int GetMilliSencodTimeDiffBetweenJstAndAndroidTime(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine($"SELECT AVG(DATEDIFF(MILLISECOND, {ColumnAndroidTime}, {ColumnJst})) AS time_diff");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
query.AppendLine($" AND {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
return(DatabaseAccesser.GetResult(query.ToString()).Rows[0].Field <int?>("time_diff") ?? 0);
}
public static DataTable Get(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine("SELECT *");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
query.AppendLine($" AND {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($"ORDER BY {ColumnJst}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static DataTable Get(InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine("SELECT *");
query.AppendLine($"FROM {TripsRawDao.TableName}");
query.AppendLine($"WHERE {TripsRawDao.ColumnDriverId} = {datum.DriverId}");
query.AppendLine($"AND {TripsRawDao.ColumnCarId} = {datum.CarId}");
query.AppendLine($"AND {TripsRawDao.ColumnSensorId} = {datum.SensorId}");
query.AppendLine($"AND {TripsRawDao.ColumnStartTime} >= '{datum.StartTime}'");
query.AppendLine($"AND {TripsRawDao.ColumnEndTime} <= '{datum.EndTime}'");
query.AppendLine($"ORDER BY {ColumnStartTime}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static bool IsExsistsTrip(DataRow row)
{
var query = new StringBuilder();
query.AppendLine($"SELECT *");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnDriverId} = {row.Field<int>(ColumnDriverId)}");
query.AppendLine($" AND {ColumnCarId} = {row.Field<int>(ColumnCarId)}");
query.AppendLine($" AND {ColumnSensorId} = {row.Field<int>(ColumnSensorId)}");
// SQL ServerではDateTime(1)型のミリ秒を切り上げするので±１秒の間をもうける
query.AppendLine($" AND {ColumnStartTime} > '{row.Field<DateTime>(ColumnStartTime).AddSeconds(-1)}'");
query.AppendLine($" AND {ColumnEndTime} < '{row.Field<DateTime>(ColumnEndTime).AddSeconds(1)}'");
return(DatabaseAccesser.GetResult(query.ToString()).AsEnumerable().Count() != 0);
}
public static DataTable GetNormalized(DateTime startTime, DateTime endTime, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine($"WITH convert_gps");
query.AppendLine($"AS (");
query.AppendLine($" SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,CONVERT(DATETIME, CONVERT(VARCHAR(30), CONVERT(DATETIME, (");
query.AppendLine($" CASE ");
query.AppendLine($" WHEN DATEPART(Ms, {ColumnJst}) >= 500");
query.AppendLine($" THEN DATEADD(SECOND, 1, {ColumnJst})");
query.AppendLine($" ELSE {ColumnJst}");
query.AppendLine($" END");
query.AppendLine($" )), 20)) AS {ColumnJst}");
query.AppendLine($" ,{ColumnLatitude}");
query.AppendLine($" ,{ColumnLongitude}");
query.AppendLine($" ,{ColumnSpeed}");
query.AppendLine($" ,{ColumnHeading}");
query.AppendLine($" ,{ColumnDistanceDifference}");
query.AppendLine($" FROM {TableName}");
query.AppendLine($" WHERE {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnCarId} = {datum.CarId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($" AND {ColumnJst} >= '{startTime}'");
query.AppendLine($" AND {ColumnJst} <= '{endTime}'");
query.AppendLine($" )");
query.AppendLine($"SELECT {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($" ,AVG({ColumnLatitude}) AS {ColumnLatitude}");
query.AppendLine($" ,AVG({ColumnLongitude}) AS {ColumnLongitude}");
query.AppendLine($" ,CAST(AVG({ColumnSpeed}) AS real) AS {ColumnSpeed}");
query.AppendLine($" ,CAST(AVG({ColumnHeading}) AS real) AS {ColumnHeading}");
query.AppendLine($" ,CAST(SUM({ColumnDistanceDifference}) AS real) AS {ColumnDistanceDifference}");
query.AppendLine($"FROM convert_gps");
query.AppendLine($"GROUP BY {ColumnDriverId}");
query.AppendLine($" ,{ColumnCarId}");
query.AppendLine($" ,{ColumnSensorId}");
query.AppendLine($" ,{ColumnJst}");
query.AppendLine($"ORDER BY {ColumnJst}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static DataTable GetLinkId(int Latitude, int Longitude)
{
int maxLatitude = Latitude + 20;
int minLatitude = Latitude - 20;
int maxLongitude = Longitude + 20;
int minLongitude = Longitude - 20;
string query = "with LINKS_TABLE as (SELECT LINKS.* ";
query += $"FROM {TableName} ,LINKS";
query += $" WHERE key_latitude >= '{minLatitude}' AND key_longitude >= '{minLongitude}' AND key_latitude <= '{maxLatitude}' AND key_longitude <= '{maxLongitude}' AND";
query += $" {TableName}.NUM = LINKS.NUM AND {TableName}.LINK_ID = LINKS.LINK_ID), ";
query += " DIRECTION as ";
query += "( ";
query += "select LINK_ID, LAT1,LON1,LAt2,LON2, ";
query += " case ";
query += " when ATN2(Y,X) * 180 / PI() >= 0 then ATN2(Y,X) * 180 / PI() ";
query += " when ATN2(Y,X) * 180 / PI() < 0 then ATN2(Y,X) * 180 / PI() + 180 ";
query += " end as HEADING,A,B,C ";
query += "from ( ";
query += " select *,ROUND((COS(RAD_LAT2) * SIN(RAD_LON2 - RAD_LON1)* 1000000),2) as Y, ROUND((COS(RAD_LAT1) * SIN(RAD_LAT2) - SIN(RAD_LAT1) * COS(RAD_LAT2) * COS(RAD_LON2 - RAD_LON1))*1000000,2) as X, LAT2-LAT1 as A,LON1-LON2 as B,LON2+LAT1-LON1*LAT2 as C ";
query += " from( ";
query += " select node1.LINK_ID, node1.LATITUDE as LAT1, node1. LONGITUDE as LON1, node2.LATITUDE as LAT2, node2.LONGITUDE as LON2, node1.LATITUDE * PI() / 180 as RAD_LAT1, node1.LONGITUDE * PI() / 180 as RAD_LON1, node2.LATITUDE * PI() / 180 as RAD_LAT2, node2.LONGITUDE * PI() / 180 as RAD_LON2 ";
query += " from ( ";
query += " select * ";
query += " from LINKS ";
query += " where NODE_ID is not null ";
query += " and DIRECTION = 1 ";
query += " ) as node1,( ";
query += " select * ";
query += " from LINKS ";
query += " where NODE_ID is not null ";
query += " and DIRECTION = 2 ";
query += " ) as node2 ";
query += " where node1.LINK_ID = node2.LINK_ID ";
query += " ) as rad ";
query += ") as XY ";
query += "where (X != 0 ";
query += "or Y != 0) ";
query += ") ";
query += "select LINKS.LINK_ID,LINKS.LATITUDE,LINKS.LONGITUDE,LAT1,LON1,LAt2,LON2,DIRECTION.HEADING,A,B,C ";
query += "from LINKS_TABLE AS LINKS ";
query += "left join DIRECTION on LINKS.LINK_ID = DIRECTION.LINK_ID ";
return(DatabaseAccesser.GetResult(query));
}
public static DataTable GetSemanticLinkTableWithHeadingAndLine(int semanticLinkId)
{
// TODO テーブル関数にすると疎結合にできるね
string query = "with DIRECTION as ";
query += "( ";
query += "select LINK_ID, LAT1,LON1,LAt2,LON2, ";
query += " case ";
query += " when ATN2(Y,X) * 180 / PI() >= 0 then ATN2(Y,X) * 180 / PI() ";
query += " when ATN2(Y,X) * 180 / PI() < 0 then ATN2(Y,X) * 180 / PI() + 180 ";
query += " end as HEADING,A,B,C ";
query += "from ( ";
query += " select *,ROUND((COS(RAD_LAT2) * SIN(RAD_LON2 - RAD_LON1)* 1000000),2) as Y, ROUND((COS(RAD_LAT1) * SIN(RAD_LAT2) - SIN(RAD_LAT1) * COS(RAD_LAT2) * COS(RAD_LON2 - RAD_LON1))*1000000,2) as X, LAT2-LAT1 as A,LON1-LON2 as B,LON2+LAT1-LON1*LAT2 as C ";
query += " from( ";
query += " select node1.LINK_ID, node1.LATITUDE as LAT1, node1. LONGITUDE as LON1, node2.LATITUDE as LAT2, node2.LONGITUDE as LON2, node1.LATITUDE * PI() / 180 as RAD_LAT1, node1.LONGITUDE * PI() / 180 as RAD_LON1, node2.LATITUDE * PI() / 180 as RAD_LAT2, node2.LONGITUDE * PI() / 180 as RAD_LON2 ";
query += " from ( ";
query += " select * ";
query += " from LINKS ";
query += " where NODE_ID is not null ";
query += " and DIRECTION = 1 ";
query += " ) as node1,( ";
query += " select * ";
query += " from LINKS ";
query += " where NODE_ID is not null ";
query += " and DIRECTION = 2 ";
query += " ) as node2 ";
query += " where node1.LINK_ID = node2.LINK_ID ";
query += " ) as rad ";
query += ") as XY ";
query += "where (X != 0 ";
query += "or Y != 0) ";
query += ") ";
query += "select SEMANTIC_LINKS.SEMANTIC_LINK_ID,SEMANTIC_LINKS.LINK_ID,LINKS.LATITUDE,LINKS.LONGITUDE,LAT1,LON1,LAT2,LON2,DIRECTION.HEADING,A,B,C ";
query += "from SEMANTIC_LINKS ";
query += "left join LINKS on LINKS.LINK_ID = SEMANTIC_LINKS.LINK_ID ";
query += "left join DIRECTION on LINKS.LINK_ID = DIRECTION.LINK_ID ";
query += "where SEMANTIC_LINK_ID = " + semanticLinkId + " ";
query += "order by SEMANTIC_LINK_ID ";
return(DatabaseAccesser.GetResult(query));
}
public static DataTable Get(DateTime startTime, DateTime endTime, int timeDiff, InsertDatum datum)
{
var query = new StringBuilder();
query.AppendLine("SELECT");
query.AppendLine($" {ColumnDriverId},");
query.AppendLine($" {ColumnCarId},");
query.AppendLine($" {ColumnSensorId},");
query.AppendLine($" CONVERT(varchar,DATEADD(MILLISECOND, {timeDiff} ,{ColumnDateTime}),121) AS jst,");
query.AppendLine($" {ColumnAccX} AS {CorrectedAccDao.ColumnLongitudinalAcc},");
query.AppendLine($" {ColumnAccY} AS {CorrectedAccDao.ColumnLateralAcc},");
query.AppendLine($" {ColumnAccZ} AS {CorrectedAccDao.ColumnVerticalAcc}");
query.AppendLine($"FROM {TableName}");
query.AppendLine($"WHERE {ColumnDateTime} >= '{startTime}'");
query.AppendLine($" AND {ColumnDateTime} <= '{endTime}'");
query.AppendLine($" AND {ColumnDriverId} = {datum.DriverId}");
query.AppendLine($" AND {ColumnSensorId} = {datum.SensorId}");
query.AppendLine($"ORDER BY {ColumnDateTime}");
return(DatabaseAccesser.GetResult(query.ToString()));
}
public static void UpdateConsumedEnergy()
{
var selectQuery = new StringBuilder();
selectQuery.AppendLine("SELECT trip.trip_id, SUM(consumed_electric_energy) AS consumed_energy");
selectQuery.AppendLine($"FROM {TableName} AS trip, {EcologMMDao.TableName} AS ecolog");
selectQuery.AppendLine("WHERE consumed_energy IS NULL");
selectQuery.AppendLine(" AND trip.trip_id = ecolog.trip_id");
selectQuery.AppendLine("GROUP BY trip.trip_id");
var resultTable = DatabaseAccesser.GetResult(selectQuery.ToString());
foreach (DataRow row in resultTable.Rows)
{
var updateQuery = new StringBuilder();
updateQuery.AppendLine($"UPDATE {TableName}");
updateQuery.AppendLine($"SET consumed_energy = '{row.Field<double>(1)}'");
updateQuery.AppendLine($"WHERE trip_id = {row.Field<int>(0)}");
DatabaseAccesser.Update(updateQuery.ToString());
}
}
public static DataTable Get()
{
string query = "SELECT * FROM " + TableName;
return(DatabaseAccesser.GetResult(query));
}
public static DataTable Get(string driverName)
{
string query = $"SELECT * FROM {TableName} WHERE {ColumnName} = '{driverName}'";
return(DatabaseAccesser.GetResult(query));
}
public static int GetMaxId()
{
string query = "SELECT MAX(" + ColumnTestId + ") as max_id FROM " + TableName;
return(DatabaseAccesser.GetResult(query).Rows[0].Field <int>("max_id"));
}
public static int GetMaxTripId()
{
string query = $"SELECT MAX({ColumnTripId}) AS max_id FROM {TableName}";
return(DatabaseAccesser.GetResult(query).Rows[0].Field <int?>("max_id") ?? 0);
}