//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}")); }