private int GetFlightID(ExponentPortalEntities ctx)
{
int iFlightID = 0;
String sVideoDate = VideoDate.ToString("yyyy-MM-dd HH:mm:ss");
String SQL = String.Empty;
SQL = $@"Select TOP 1
FlightID
FROM
FlightMapData
WHERE
CreatedTime BETWEEN DATEADD(MINUTE, -10, '{sVideoDate}') AND DATEADD(MINUTE,10, '{sVideoDate}') AND
DroneID={DroneID}
ORDER BY
ABS(DATEDIFF(SECOND, CreatedTime, '{sVideoDate}')) ASC";
using (var cmd = ctx.Database.Connection.CreateCommand()) {
cmd.CommandText = SQL;
var oFlightID = cmd.ExecuteScalar();
if (oFlightID == null)
{
return(iFlightID);
}
int.TryParse(oFlightID.ToString(), out iFlightID);
}
return(iFlightID);
}
private bool SetFlightID(ExponentPortalEntities ctx)
{
String SQL;
String VideoURL = path.Substring(path.LastIndexOf('/') + 1);
SQL = $@"DELETE FROM DroneFlightVideo
WHERE
VideoURL='{VideoURL}' AND
FlightID={FlightID}";
DoSQL(SQL, ctx);
SQL = $@"UPDATE DroneFlight SET
RecordedVideoURL='{VideoURL}'
WHERE
ID={FlightID}";
DoSQL(SQL, ctx);
SQL = $@"INSERT INTO DroneFlightVideo(
DroneID, FlightID, VideoURL, CreatedDate, VideoDateTime
) VALUES (
{DroneID}, {FlightID}, '{VideoURL}', GETDATE(), '{VideoDate.ToString("yyyy-MM-dd HH:mm:ss")}'
)";
DoSQL(SQL, ctx);
return(true);
}
