// 30일 전 데이터 가져오기
// GET: api/data?MeasureType=Strain
public IEnumerable <MDataModel> Get(string MeasureType)
{
var dataList = new List <MDataModel>();
if (MeasureType.Equals("Strain") || MeasureType.Equals("Temp") || MeasureType.Equals("Disp"))
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
conn.Open();
try
{
DateTime now = DateTime.Now;
int year_30DaysAgo = now.AddDays(-30).Year; // 30일 전에 년도
for (int year = now.Year; year > year_30DaysAgo - 1; year--)
{
StringBuilder query = new StringBuilder(700);
query.Append("SELECT MEASURE_TIME");
int endIdx = 0;
if (MeasureType.Equals("Strain"))
{
endIdx = CRUD.Strain_colName.Count + 1;
}
else if (MeasureType.Equals("Temp"))
{
endIdx = CRUD.Temp_colName.Count + 1;
}
else if (MeasureType.Equals("Disp"))
{
endIdx = CRUD.Disp_colName.Count + 1;
}
for (int colNo = 1; colNo < endIdx; colNo++)
{
query.Append($", RESULT{colNo}");
}
// 30일전까지 데이터 가져오기
query.Append($" FROM {MeasureType}_{year.ToString()} WHERE TO_DAYS(NOW()) - TO_DAYS(MEASURE_TIME) <= 30 ORDER BY MEASURE_TIME ASC");
//logger.Debug(query);
cmd.CommandText = query.ToString();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MDataModel data = new MDataModel();
StringBuilder mData = new StringBuilder(350);
data.MTime = (DateTime)reader[0];
int last = reader.FieldCount;
for (int i = 1; i < last; i++)
{
if (reader[i] == DBNull.Value)
{
mData.Append("NaN,");
}
else
{
mData.Append($"{reader[i]},");
}
}
data.MData = mData.ToString().TrimEnd(',');
dataList.Add(data);
}
reader.Close();
}
}
catch (Exception ex)
{
logger.Error("Error Data Request for 30 days", ex);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
}
return(dataList);
}
/// <summary>
/// Raw 데이터 가져오는 메서드
/// </summary>
/// <param name="DataType"></param>
/// <param name="Start"></param>
/// <param name="End"></param>
/// <returns></returns>
public IEnumerable <MDataModel> Get(string DataType, DateTime Start, DateTime End)
{
var dataList = new List <MDataModel>();
int startYear = Start.Year;
int year = End.Year;
int idxCnt = 52; // 사일로 1번,3번의 채널수
if (DataType.Equals("p1_raw") || DataType.Equals("p2_raw") || DataType.Equals("p3_raw") || DataType.Equals("p4_raw"))
{
if (DataType.Equals("p2_raw") || DataType.Equals("p4_raw"))
{
idxCnt = 40; // 사일로 2번, 4번 채널수
}
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
StringBuilder query = new StringBuilder(700);
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
conn.Open();
try
{
while (year >= startYear)
{
query.Append("SELECT MEASURE_TIME, BATT_VOLT_MIN, TEMP");
for (int i = 1; i <= idxCnt; i++)
{
query.Append($", SENSOR{i}");
}
query.Append($" FROM {DataType}_{year.ToString()} WHERE MEASURE_TIME >= '{Start}' AND MEASURE_TIME <= '{End}' ORDER BY MEASURE_TIME ASC");
cmd.CommandText = query.ToString();
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MDataModel data = new MDataModel();
StringBuilder mData = new StringBuilder();
data.MTime = (DateTime)reader[0];
for (int i = 1; i < reader.FieldCount; i++)
{
if (reader[i] == DBNull.Value)
{
mData.Append("NaN,");
}
else
{
mData.Append($"{reader[i]},");
}
}
data.MData = mData.ToString().TrimEnd(',');
dataList.Add(data);
}
reader.Close();
year--;
}
}
catch (Exception ex)
{
logger.Error("Error Getting Raw Data", ex);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
}
return(dataList);
}
/// <summary>
/// 결과 데이터(변형율, 온도, 경사도)값 가져오는 함수
/// </summary>
/// <param name="DataType"></param>
/// <param name="SensorIdx"></param>
/// <param name="Start"></param>
/// <param name="End"></param>
/// <returns></returns>
public IEnumerable <MDataModel> Get(string DataType, string SensorIdx, DateTime Start, DateTime End)
{
var dataList = new List <MDataModel>();
string[] index = SensorIdx.Split('s');
int startYear = Start.Year;
int year = End.Year;
if (DataType.Equals("strain") || DataType.Equals("disp") || DataType.Equals("temp"))
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
string query = null;
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
conn.Open();
try
{
while (year >= startYear)
{
query = "SELECT MEASURE_TIME";
for (int i = 1; i < index.Length; i++)
{
query += $", RESULT{index[i]}";
}
query += $" FROM {DataType}_{year.ToString()} WHERE MEASURE_TIME >= '{Start}' AND MEASURE_TIME <= '{End}' ORDER BY MEASURE_TIME ASC";
cmd.CommandText = query;
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MDataModel data = new MDataModel();
string mData = null;
data.MTime = (DateTime)reader[0];
for (int i = 1; i < reader.FieldCount; i++)
{
if (reader[i] == DBNull.Value)
{
mData += "NaN,";
}
else
{
mData += $"{reader[i]},";
}
}
data.MData = mData.TrimEnd(',');
dataList.Add(data);
}
reader.Close();
year--;
}
}
catch (Exception ex)
{
logger.Error("Error Getting Trend Chart Data", ex);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
}
else if (DataType.Equals("p1_raw") || DataType.Equals("p2_raw") || DataType.Equals("p3_raw") || DataType.Equals("p4_raw"))
{
using (OdbcConnection conn = new OdbcConnection(connectionString))
{
string query = null;
OdbcCommand cmd = new OdbcCommand();
cmd.Connection = conn;
conn.Open();
try
{
while (year >= startYear)
{
query = "SELECT MEASURE_TIME, BATT_VOLT_MIN, TEMP";
for (int i = 1; i < index.Length; i++)
{
query += $", SENSOR{index[i]}";
}
query += $" FROM {DataType}_{year.ToString()} WHERE MEASURE_TIME >= '{Start}' AND MEASURE_TIME <= '{End}' ORDER BY MEASURE_TIME ASC";
cmd.CommandText = query;
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
MDataModel data = new MDataModel();
string mData = null;
data.MTime = (DateTime)reader[0];
for (int i = 1; i < reader.FieldCount; i++)
{
if (reader[i] == DBNull.Value)
{
mData += "NaN,";
}
else
{
mData += $"{reader[i]},";
}
}
data.MData = mData.TrimEnd(',');
dataList.Add(data);
}
reader.Close();
year--;
}
}
catch (Exception ex)
{
logger.Error("Error Getting Raw Data", ex);
}
finally
{
if (conn != null)
{
conn.Dispose();
}
}
}
}
return(dataList);
}
