private string GetInsertSQL(SeriesDataModel.TimeSeriesData input, SeriesDataModel.Point pt)
{
// MANUAL SQL
string sqlString = "insert into " + input.series.tablename + " (datetime,value,flag) values " +
"(str_to_date('" + pt.datetime + "','%m/%d/%Y %r'),'" + pt.value + "','" + pt.flag + "')";
return(sqlString);
}
private string GetUpdateSQL(SeriesDataModel.TimeSeriesData input, SeriesDataModel.Point pt)
{
// MANUAL SQL
string sqlString = "update " + input.series.tablename + " set value='" +
pt.value + "' where datetime=" + "str_to_date('" + pt.datetime + "','%m/%d/%Y %r')";
return(sqlString);
}
private string GetMassInsertSQL(SeriesDataModel.TimeSeriesData input)
{
// MANUAL SQL LOOP
string sqlString = "insert into " + input.series.tablename + " (datetime,value,flag) values ";
foreach (SeriesDataModel.Point pt in input.data)
{
sqlString += "(str_to_date('" + pt.datetime + "','%m/%d/%Y %r'),'" + pt.value + "','" + pt.flag + "'),";
}
sqlString = sqlString.Remove(sqlString.Length - 1);
return(sqlString);
}
private string GetDeleteSQL(SeriesDataModel.TimeSeriesData input)
{
// MANUAL SQL LOOP
string sqlString = "delete from " + input.series.tablename + " where datetime in (";
foreach (SeriesDataModel.Point pt in input.data)
{
sqlString += "str_to_date('" + pt.datetime + "','%m/%d/%Y %r'),";
}
sqlString = sqlString.Remove(sqlString.Length - 1);
sqlString += ")";
return(sqlString);
}
public SeriesDataModel.TimeSeriesData GetSeriesData(string seriesTable, DateTime t1, DateTime t2)
{
IDbConnection db = Database.Connect();
// Get TS data points
string sqlStringData = "select * from " + seriesTable + " where datetime >= str_to_date('" + t1 + "','%m/%d/%Y %r') " +
"and datetime <= str_to_date('" + t2 + "','%m/%d/%Y %r') ";
List <SeriesDataModel.Point> tsData = (List <SeriesDataModel.Point>)db.Query <SeriesDataModel.Point>(sqlStringData);
// Get TS metadata
string sqlStringMeta = "select * from seriescatalog a, sitecatalog b, parametercatalog c " +
"where a.siteid=b.siteid and a.parameter=c.id and a.tablename = '" + seriesTable + "'";
//List<SeriesDataModel.PiscesTimeSeriesData> ts = (List<SeriesDataModel.PiscesTimeSeriesData>)db.Query<SeriesDataModel.PiscesTimeSeriesData>(sqlStringMeta);
var ts = (List <SeriesDataModel.TimeSeriesData>)db.Query < //Dapper MultiMap
SeriesDataModel.TimeSeriesData, //a
SeriesModel.Series, //b
SiteModel.PiscesSite, //c
ParameterModel.PiscesParameter, //d
SeriesDataModel.TimeSeriesData>(
sqlStringMeta,
(a, b, c, d) =>
{
a = new SeriesDataModel.TimeSeriesData
{
series = b,
site = c,
parameter = d,
data = tsData//new List<SeriesDataModel.Point>()
};
return(a);
},
commandType: CommandType.Text,
splitOn: "siteid,id"
);
return(ts[0]);
}
