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