// insert detection query
public async Task<bool> InsertDetection(Detection detection)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@WeatherId", detection.WeatherId);
_params.Add("@CameraId", detection.CameraId);
_params.Add("@ImagePath", detection.ImagePath);
_params.Add("@Date", detection.Date);
string sql = " INSERT INTO " + bods.bods.Tdetection
+ " (" + bods.bods.Tdetection.FWeatherId.Cn
+ " ," + bods.bods.Tdetection.FCameraId.Cn
+ " ," + bods.bods.Tdetection.FImagePath.Cn
+ " ," + bods.bods.Tdetection.FDate.Cn
+ ")"
+ " VALUES"
+ " (@WeatherId"
+ " ,@CameraId"
+ " ,@ImagePath"
+ " ,@Date"
+ ");";
using (IDbConnection conn = DataLayer.NewConnection())
{
return Convert.ToBoolean(await conn.ExecuteAsync(sql, _params));
}
}
// Insert camera query
public async Task <int> InsertCamera(Camera camera)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@Latitude", camera.Latitude);
_params.Add("@Longitude", camera.Longitude);
_params.Add("@LocationName", camera.LocationName);
_params.Add("@IsWorking", camera.IsWorking);
_params.Add("@DateAdded", camera.DateAdded);
string sql = " INSERT INTO " + bods.bods.Tcamera
+ " (" + bods.bods.Tcamera.FLatitude.Cn
+ " ," + bods.bods.Tcamera.FLongitude.Cn
+ " ," + bods.bods.Tcamera.FLocationName.Cn
+ " ," + bods.bods.Tcamera.FIsWorking.Cn
+ " ," + bods.bods.Tcamera.FDateAdded.Cn
+ ")"
+ " VALUES"
+ " (@Latitude"
+ " ,@Longitude"
+ " ,@LocationName"
+ " ,@IsWorking"
+ " ,@DateAdded"
+ ") ;";
using (IDbConnection conn = DataLayer.NewConnection())
{
Convert.ToBoolean(await conn.ExecuteAsync(sql, _params));
var id = conn.Query <int>("SELECT CAST(LAST_INSERT_ID();").Single();
return(id);
}
}
// get all weather raws from DB query
public async Task <List <Weather> > GetWeather()
{
DynamicParameters _params = new DynamicParameters();
string sql = " SELECT * FROM " + bods.bods.Tweather;
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <Weather>(sql, _params)).ToList());
}
}
// get list by user id query
public async Task <List <UserCamera> > GetByUserId(int userId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@UserId", userId);
string sql = " SELECT * FROM " + bods.bods.Tusercamera
+ " WHERE " + bods.bods.Tusercamera.FUserId.Cn + " =@UserId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <UserCamera>(sql, _params)).ToList());
}
}
// get camera by camera id query
public async Task <Camera> GetCameraById(int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@CameraId", cameraId);
string sql = " SELECT * FROM " + bods.bods.Tcamera
+ " WHERE " + bods.bods.Tcamera.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <Camera>(sql, _params)).ToList().FirstOrDefault());
}
}
// get weather by weather id value query
public async Task <List <Weather> > GetWeatherById(int weatherId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@WeatherId", weatherId);
string sql = " SELECT * FROM " + bods.bods.Tweather
+ " WHERE " + bods.bods.Tweather.FWeatherId.Cn + " =@WeatherId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <Weather>(sql, _params)).ToList());
}
}
// Delete camera query
public async Task <bool> DeleteCamera(int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@CameraId", cameraId);
string sql = " DELETE FROM " + bods.bods.Tcamera
+ " WHERE " + bods.bods.Tcamera.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// get user by user name query
public async Task <User> GetByUserName(string userName)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@UserName", userName);
string sql = " SELECT * FROM " + bods.bods.Tuser
+ " WHERE " + bods.bods.Tuser.FUserName.Cn + " =@UserName";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <User>(sql, _params)).ToList().FirstOrDefault());
}
}
// get users by adminId query
public async Task <List <User> > GetAllChildUsers(int adminId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@AdminId", adminId);
string sql = " SELECT * FROM " + bods.bods.Tuser
+ " WHERE " + bods.bods.Tuser.FAdminId.Cn + " =@AdminId;";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <User>(sql, _params)).ToList());
}
}
// get detection by camera id query
public async Task<List<Detection>> GetDetectionByCameraId(int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@CameraId", cameraId);
string sql = " SELECT * FROM " + bods.bods.Tdetection
+ " WHERE " + bods.bods.Tdetection.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return (await conn.QueryAsync<Detection>(sql, _params)).ToList();
}
}
// Update camera working status values query
public async Task <bool> ChangeWorkingStatus(bool isWorking, int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@IsWorking", isWorking);
_params.Add("@CameraId", cameraId);
string sql = " UPDATE " + bods.bods.Tcamera + " SET "
+ bods.bods.Tcamera.FIsWorking.Cn + " =@IsWorking "
+ " WHERE " + bods.bods.Tcamera.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// get camera by location values query
public async Task <Camera> GetCameraByLocation(double latitude, double longitude)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@Latitude", latitude);
_params.Add("@Longitude", longitude);
string sql = " SELECT * FROM " + bods.bods.Tcamera
+ " WHERE " + bods.bods.Tcamera.FLatitude.Cn + " LIKE @Latitude"
+ " AND " + bods.bods.Tcamera.FLongitude.Cn + " LIKE @Longitude";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <Camera>(sql, _params)).ToList().FirstOrDefault());
}
}
// get weather by location values query
public async Task <List <Weather> > GetWeatherByLocation(float latitude, float longitude)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@Latitude", latitude);
_params.Add("@Longitude", longitude);
string sql = " SELECT * FROM " + bods.bods.Tweather
+ " WHERE " + bods.bods.Tweather.FLatitude.Cn + " =@Latitude"
+ " AND " + bods.bods.Tweather.FLongitude.Cn + " =@Longitude";
using (IDbConnection conn = DataLayer.NewConnection())
{
return((await conn.QueryAsync <Weather>(sql, _params)).ToList());
}
}
// update IsAdmin to true query
public async Task <bool> SetUserAsAdmin(int UserId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@UserId", UserId);
_params.Add("@IsAdmin", true);
string sql = " Update " + bods.bods.Tuser + " SET "
+ bods.bods.Tuser.FIsAdmin.Cn + " =@IsAdmin"
+ " WHERE " + bods.bods.Tuser.FUserId.Cn + " =@UserId;";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// update user id query
public async Task <bool> UpdateUserId(int userId, int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@UserId", userId);
_params.Add("@CameraId", cameraId);
string sql = " UPDATE" + bods.bods.Tusercamera + " SET "
+ bods.bods.Tusercamera.FUserId.Cn + " =@UserId"
+ " WHERE " + bods.bods.Tusercamera.FCameraId.Cn + " =@CameraId ";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// insert user query
public async Task <bool> InsertUser(User user)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@AdminId", user.AdminId);
_params.Add("@CreationDate", user.CreationDate);
_params.Add("@FirstName", user.FirstName);
_params.Add("@LastName", user.LastName);
_params.Add("@UserName", user.UserName);
_params.Add("@IsAdmin", user.IsAdmin);
_params.Add("@IsSetPasswordAllowed", user.IsSetPasswordAllowed);
_params.Add("@UserGuid", user.UserGuid);
_params.Add("@Phone", user.Phone);
_params.Add("@SlackWebHook", user.SlackWebHook);
string sql = " INSERT INTO " + bods.bods.Tuser
+ " (" + bods.bods.Tuser.FAdminId.Cn
+ " ," + bods.bods.Tuser.FCreationDate.Cn
+ " ," + bods.bods.Tuser.FFirstName.Cn
+ " ," + bods.bods.Tuser.FLastName.Cn
+ " ," + bods.bods.Tuser.FUserName.Cn
+ " ," + bods.bods.Tuser.FIsAdmin.Cn
+ " ," + bods.bods.Tuser.FIsSetPasswordAllowed.Cn
+ " ," + bods.bods.Tuser.FUserGuid.Cn
+ " ," + bods.bods.Tuser.FPhone.Cn
+ " ," + bods.bods.Tuser.FSlackWebHook.Cn
+ ")"
+ " VALUES"
+ " (@AdminId"
+ " ,@CreationDate"
+ " ,@FirstName"
+ " ,@LastName"
+ " ,@UserName"
+ " ,@IsAdmin"
+ " ,@IsSetPasswordAllowed"
+ " ,@UserGuid"
+ " ,@Phone"
+ " ,@SlackWebHook"
+ ");";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// Update camera details query by camera Id
public async Task <bool> UpdateCamera(Camera camera)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@CameraId", camera.CameraId);
_params.Add("@Latitude", camera.Latitude);
_params.Add("@Longitude", camera.Longitude);
_params.Add("@LocationName", camera.LocationName);
string sql = " UPDATE " + bods.bods.Tcamera + " SET "
+ bods.bods.Tcamera.FLatitude.Cn + " =@Latitude, "
+ bods.bods.Tcamera.FLongitude.Cn + " =@Longitude, "
+ bods.bods.Tcamera.FLocationName.Cn + " =@LocationName "
+ " WHERE " + bods.bods.Tcamera.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// update user password query
public async Task <bool> UpdatePassword(string hashedPassword, string UserGuid, byte[] saltPassword)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@HashedPassword", hashedPassword);
_params.Add("@Guid", UserGuid);
_params.Add("@SaltPassword", saltPassword);
_params.Add("@IsSetPasswordAllowed", false);
string sql = " Update " + bods.bods.Tuser + " SET "
+ bods.bods.Tuser.FHashedPassword.Cn + " =@HashedPassword, "
+ bods.bods.Tuser.FSaltPassword.Cn + " =@SaltPassword, "
+ bods.bods.Tuser.FIsSetPasswordAllowed.Cn + " =@IsSetPasswordAllowed"
+ " WHERE " + bods.bods.Tuser.FUserGuid.Cn + " =@Guid;";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// insert usercamera query
public async Task <bool> InsertUserCamera(int userId, int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@UserId", userId);
_params.Add("@CameraId", cameraId);
string sql = " INSERT INTO " + bods.bods.Tusercamera
+ " (" + bods.bods.Tusercamera.FUserId.Cn
+ " ," + bods.bods.Tusercamera.FCameraId.Cn
+ ")"
+ " VALUES"
+ " (@UserId"
+ " ,@CameraId"
+ ");";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// insert weather query
public async Task <ulong> InsertWeather(Weather weather)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@Latitude", weather.Latitude);
_params.Add("@Longitude", weather.Longitude);
_params.Add("@LocationName", weather.LocationName);
_params.Add("@WindSpeed", weather.WindSpeed);
_params.Add("@Date", weather.Date);
_params.Add("@Description", weather.Description);
string sql = " INSERT INTO " + bods.bods.Tweather
+ " (" + bods.bods.Tweather.FLatitude.Cn
+ " ," + bods.bods.Tweather.FLongitude.Cn
+ " ," + bods.bods.Tweather.FLocationName.Cn
+ " ," + bods.bods.Tweather.FWindSpeed.Cn
+ " ," + bods.bods.Tweather.FDate.Cn
+ " ," + bods.bods.Tweather.FDescription.Cn
+ ")"
+ " VALUES"
+ " (@Latitude"
+ " ,@Longitude"
+ " ,@LocationName"
+ " ,@WindSpeed"
+ " ,@Date"
+ " ,@Description"
+ ");";
using (IDbConnection conn = DataLayer.NewConnection())
{
Convert.ToBoolean(await conn.ExecuteAsync(sql, _params));
var id = conn.Query <ulong>("SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER);").Single();
return(id);
}
}
// update user query
public async Task <bool> UpdateUser(UpdateUserDetails user)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@FirstName", user.FirstName);
_params.Add("@LastName", user.LastName);
_params.Add("@UserName", user.UserName);
_params.Add("@UserId", user.UserId);
_params.Add("@Phone", user.Phone);
_params.Add("@SlackWebHook", user.SlackWebHook);
string sql = " UPDATE " + bods.bods.Tuser + " SET "
+ bods.bods.Tuser.FFirstName.Cn + " =@FirstName, "
+ bods.bods.Tuser.FLastName.Cn + " =@LastName, "
+ bods.bods.Tuser.FPhone.Cn + " =@Phone, "
+ bods.bods.Tuser.FSlackWebHook.Cn + " =@SlackWebHook, "
+ bods.bods.Tuser.FUserName.Cn + " =@UserName "
+ " WHERE " + bods.bods.Tuser.FUserId.Cn + " =@UserId ";
using (IDbConnection conn = DataLayer.NewConnection())
{
return(Convert.ToBoolean(await conn.ExecuteAsync(sql, _params)));
}
}
// read detection left joins query
public async Task<List<DetectionResponse>> ReadDetection(DateTime fromDate, DateTime toDate , int cameraId)
{
DynamicParameters _params = new DynamicParameters();
_params.Add("@ToDate", toDate);
_params.Add("@FromDate", fromDate);
_params.Add("@CameraId", cameraId);
string sql = " SELECT " + bods.bods.Tdetection + ".*, " + bods.bods.Tweather + ".*, " + bods.bods.Tcamera + ".* "
+ " FROM " + bods.bods.Tdetection
+ " LEFT JOIN " + bods.bods.Tweather + " ON " + bods.bods.Tweather.FWeatherId.Cn + " = " + bods.bods.Tdetection.FWeatherId.Cn
+ " LEFT JOIN " + bods.bods.Tcamera + " ON " + bods.bods.Tcamera.FCameraId.Cn + " = " + bods.bods.Tdetection.FCameraId.Cn
+ " WHERE " + bods.bods.Tdetection.FDate.Cn + " >=@FromDate"
+ " AND " + bods.bods.Tdetection.FDate.Cn + " <=@ToDate"
+ " AND " + bods.bods.Tdetection.FCameraId.Cn + " =@CameraId";
using (IDbConnection conn = DataLayer.NewConnection())
{
// determine query response stracture
return (await conn.QueryAsync<DetectionResponse>(sql,
new[] {
typeof(int), // 0 DetectionId
typeof(int), // 1 CameraId
typeof(string), // 2 ImagePath
typeof(int), // 3 WeatherId
typeof(DateTime), // 4 Date
typeof(int), // 5 WeatherId
typeof(string), // 6 WeatherlocationName
typeof(double), // 7 Longitude
typeof(double), // 8 Latitude
typeof(DateTime), // 9 Date
typeof(double), // 10 WindSpeed
typeof(string), // 11 Description
typeof(int), // 12 CameraId
typeof(string), // 13 CameraLocationName
typeof(double), // 14 Longitude
typeof(double), // 15 Latitude
typeof(bool), // 16 IsWorking
typeof(DateTime) // 17 DateAdded
}, objects =>
{
// create object
DetectionResponse detectionResponse = new DetectionResponse()
{
CameraId = objects[1] as int? ?? 0,
ImagePath = objects[2] as string,
Date = objects[4] as DateTime? ?? DateTime.UtcNow,
WeatherLocationName = objects[6] as string,
Longitude = objects[7] as double? ?? 0,
Latitude = objects[8] as double? ?? 0,
WindSpeed = objects[10] as double? ?? 0,
Description = objects[11] as string,
CameraLocationName = objects[13] as string
};
return detectionResponse;
}
, _params
, splitOn: $@"{bods.bods.Tdetection.FCameraId.OnlyColumnName},
{bods.bods.Tdetection.FImagePath.OnlyColumnName},
{bods.bods.Tdetection.FWeatherId.OnlyColumnName},
{bods.bods.Tdetection.FDate.OnlyColumnName},
{bods.bods.Tweather.FWeatherId.OnlyColumnName},
{bods.bods.Tweather.FLocationName.OnlyColumnName},
{bods.bods.Tweather.FLongitude.OnlyColumnName},
{bods.bods.Tweather.FLatitude.OnlyColumnName},
{bods.bods.Tweather.FDate.OnlyColumnName},
{bods.bods.Tweather.FWindSpeed.OnlyColumnName},
{bods.bods.Tweather.FDescription.OnlyColumnName},
{bods.bods.Tcamera.FCameraId.OnlyColumnName},
{bods.bods.Tcamera.FLocationName.OnlyColumnName},
{bods.bods.Tcamera.FLongitude.OnlyColumnName},
{bods.bods.Tcamera.FLatitude.OnlyColumnName},
{bods.bods.Tcamera.FIsWorking.OnlyColumnName},
{bods.bods.Tcamera.FDateAdded.OnlyColumnName}"
)).ToList();
}
}