//takto lze vypsat cas v lokalnim formatovani:
// select time(timeStamp,'localtime'),time(timeStamp,'utc'),date(timeStamp,'localtime'), datetime(timeStamp,'localtime'), unitId from observations order by timeStamp
public static int ExportToCsv(IntervalExport export)
{
string sql =
"select datetime(obTime,'localtime') as time,cmd,unitId,isPrestavka,cilOk,pocetOk,casPoslednihoOk,prumCasVyrobyOk,cilNg,pocetNg,casPoslednihoNg,prumCasVyrobyNg,rozdil,atualniDefectivita,stavLinky from observations " +
"where obTime between @timeFrom and @timeTo";
if (!export.ExportAll)
{
sql += " and unitId = @unitId";
}
using (SQLiteCommand command = new SQLiteCommand(sql, _dbConnection))
{
command.Parameters.Add(new SQLiteParameter("@timeFrom", export.TimeFrom.ToUniversalTime()));
command.Parameters.Add(new SQLiteParameter("@timeTo", export.TimeTo.ToUniversalTime()));
if (!export.ExportAll)
{
command.Parameters.Add(new SQLiteParameter("@unitId", export.UnitId));
}
DataTable data = new DataTable();
SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(command);
//myAdapter.SelectCommand = myCommand;
myAdapter.Fill(data);
return(CreateCsvFile(data, export.FileName));
}
}
public IHttpActionResult ExportInterval([FromBody] IntervalExport value)
{
Logger.Info($"exportFilename:{value.FileName} from:{value.TimeFromStr} to:{value.TimeToStr}");
if (string.IsNullOrWhiteSpace(value.FileName) || string.IsNullOrWhiteSpace(value.TimeFromStr) ||
string.IsNullOrWhiteSpace(value.TimeToStr))
{
return(BadRequest("Neplatné parametry"));
}
var cnt = SqlliteDao.ExportToCsv(value);
return(Ok($"Počet exportovaných záznamů: {cnt}"));
}
