public bool SetEmailConfirmed(string username, bool confirmed)
{
try
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("SetUserEmailConfirmed")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("email_confirmed", confirmed),
new SqlParameter("username", username)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
return(true);
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
}
public bool UpdateDatasetRepresentationLimits(string datasetName,
string username,
decimal?minimumHeight,
decimal?maximumHeight,
decimal?minimumDeformationRate,
decimal?maximumDeformationRate,
decimal?minimumStdDev,
decimal?maximumStdDev)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("UpdatePointsDatasetLimits")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("datasetName", datasetName),
new SqlParameter("username", username),
new SqlParameter("minimum_height", minimumHeight),
new SqlParameter("minimum_def_rate", minimumDeformationRate),
new SqlParameter("minimum_std_dev", minimumStdDev),
new SqlParameter("maximum_height", maximumHeight),
new SqlParameter("maximum_def_rate", maximumDeformationRate),
new SqlParameter("maximum_std_dev", maximumStdDev)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
return(false);
}
return(true);
}
public bool InsertUser(User user)
{
try
{
return((int)SqlExecutionInstance.ExecuteScalar(new SqlCommand("InsertUser")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("hashed_password", user.PasswordHash),
new SqlParameter("username", user.Username),
new SqlParameter("first_name", user.FirstName),
new SqlParameter("last_name", user.LastName),
new SqlParameter("email", user.Email),
new SqlParameter("secure_stamp", user.SecurityStamp)
},
new SqlConnection(this.connectionString))
== 1);
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
}
public int GetColorMapID(string username, string colorMapName)
{
try
{
Query query = new Query($"{Tables.ColorPalettesTable} as CP")
.Select("color_palette_id")
.Join($"{Tables.Users} as U", "U.user_id", "CP.user_id")
.Where("CP.palette_name", colorMapName)
.Where("U.username", username);
SqlResult queryResult = new SqlServerCompiler().Compile(query);
return(Convert.ToInt32(
SqlExecutionInstance.ExecuteScalar(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
},
null,
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(-1);
}
}
private void log(string message, string stacktrace, LogTrigger logTrigger, LogType logType)
{
try
{
var query = new Query(logTable)
.AsInsert(new
{
stacktrace,
message,
log_type = logType.ToString(),
log_trigger = logTrigger.ToString(),
creation_date = DateTime.Now,
});
SqlResult queryResult = new SqlServerCompiler().Compile(query);
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand(queryResult.Sql),
new SqlParameter[] {
new SqlParameter("p0", queryResult.Bindings[0]),
new SqlParameter("p1", queryResult.Bindings[1]),
new SqlParameter("p2", queryResult.Bindings[2]),
new SqlParameter("p3", queryResult.Bindings[3]),
new SqlParameter("p4", queryResult.Bindings[4])
},
new SqlConnection(this.connectionString));
}
catch { }
}
public IEnumerable <User> GetUsersFiltered(IEnumerable <Tuple <UserFilters, string> > filters, int pageIndex, int itemsPerPage)
{
Query query = new Query("dbo.Users as U")
.Select(new string[] {
"U.username",
"UD.first_name",
"UD.last_name",
"UD.timestamp",
"UD.email",
"UD.email_confirmed"
})
.Join("dbo.UsersDetails as UD", "UD.user_id", "U.user_id");
Func <UserFilters, string> getColumnName = (filter) =>
{
switch (filter)
{
case UserFilters.Email:
return("UD.email");
case UserFilters.FirstName:
return("UD.first_name");
case UserFilters.LastName:
return("UD.last_name");
case UserFilters.Username:
return("U.username");
default:
return(null);
}
};
if (filters != null)
{
foreach (var filter in filters)
{
string columnName = getColumnName(filter.Item1);
if (!string.IsNullOrEmpty(columnName))
{
query = query.WhereLike(columnName, $"%{filter.Item2}%");
}
}
}
query = query.OrderByDesc("UD.user_id")
.Limit(itemsPerPage).Offset(pageIndex * itemsPerPage);
SqlResult queryResult = new SqlServerCompiler().Compile(query);
using (var usersResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
}, null, new SqlConnection(this.connectionString)))
{
return(parseUserDataset(usersResult.Tables[0].Rows));
}
}
public IEnumerable <Tuple <string, ColorMap> > GetColorMapsFiltered(IEnumerable <Tuple <ColorMapFilters, string> > filters, int pageIndex, int itemsPerPage)
{
Query query = new Query("dbo.ColorPalettes as CP")
.Select(new string[] {
"U.username",
"CP.palette_name",
"CP.palette_serialization",
"CP.status_mask",
"CP.main_color_criteria"
})
.Join("dbo.Users as U", "CP.user_id", "U.user_id");
Func <ColorMapFilters, string> getColumnName = (filter) =>
{
switch (filter)
{
case ColorMapFilters.ColorMapName:
return("CP.palette_name");
case ColorMapFilters.Username:
return("U.username");
default:
return(null);
}
};
if (filters != null)
{
foreach (var filter in filters)
{
string columnName = getColumnName(filter.Item1);
if (!string.IsNullOrEmpty(columnName))
{
query = query.WhereLike(columnName, $"%{filter.Item2}%");
}
}
}
query = query.OrderByDesc("CP.creation_date")
.Limit(itemsPerPage).Offset(pageIndex * itemsPerPage);
SqlResult queryResult = new SqlServerCompiler().Compile(query);
using (var colorMapsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
}, null, new SqlConnection(this.connectionString)))
{
return(parseColorMapDataset(colorMapsResult.Tables[0].Rows));
}
}
public int GetDatasetsCount()
{
Query query = new Query(Tables.Datasets).AsCount("data_set_id");
SqlResult queryResult = new SqlServerCompiler().Compile(query);
return(Convert.ToInt32(
SqlExecutionInstance.ExecuteScalar(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
},
null,
new SqlConnection(this.connectionString))));
}
public IEnumerable <Tuple <string, ColorMap> > GetGeoserverColorMaps(int geoserverDatasetId)
{
using (var colorMapsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetGeoserverColorPalettes")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("@geoserver_dataset_id", geoserverDatasetId)
},
new SqlConnection(this.connectionString)))
{
return(parseColorMapDataset(colorMapsResult.Tables[0].Rows));
}
}
private PointsDataSetHeader getDatasetHeader(string username, string datasetName, int datasetId)
{
using (var userCredentialsInfo = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetUserPointsDataset")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("username", username),
new SqlParameter("dataset_name", datasetName),
new SqlParameter("dataset_id", datasetId),
},
new SqlConnection(this.connectionString)))
{
return(parseDataSetHeaderDataset(userCredentialsInfo.Tables[0].Rows));
};
}
public bool RemovePointsDataset(string username, string datasetName)
{
Query query = new Query(Tables.Datasets)
.AsDelete()
.Where("dataset_name", datasetName)
.Where("user_id", new Query(Tables.Users).Select("id").Where("username", username));
SqlResult queryResult = new SqlServerCompiler().Compile(query);
return
(SqlExecutionInstance.ExecuteScalar(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
},
null,
new SqlConnection(this.connectionString))
!= null);
}
public IEnumerable <Tuple <string, ColorMap> > GetColorMapsFiltered(ColorMapFilters filter, string filterValue, int pageIndex, int itemsPerPage)
{
using (var colorMapsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetColorPalettesFiltered")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("@filter_id", (int)filter),
new SqlParameter("@filter_value", filterValue),
new SqlParameter("@page_index", pageIndex),
new SqlParameter("@items_per_page", itemsPerPage)
},
new SqlConnection(this.connectionString)))
{
return(parseColorMapDataset(colorMapsResult.Tables[0].Rows));
}
}
public byte[] GetUserHashedPassword(string username)
{
if (string.IsNullOrEmpty(username))
{
return(null);
}
byte[] passwordHash = (byte[])SqlExecutionInstance.ExecuteScalar(new SqlCommand("GetUserPasswordInfo")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("username", username)
},
new SqlConnection(this.connectionString));
return(passwordHash);
}
public int GetUserAssociatedDatasetsCount(string username)
{
IList <UserRoles> roles = this.GetUserRoles(username);
Query query = new Query($"{Tables.Datasets} as D")
.AsCount()
.LeftJoin($"{Tables.UsersAllowedDatasets} as UAD", "D.data_set_id", "UAD.dataset_id")
.WhereRaw($"(UAD.user_id = (select top 1 user_id from Users as _U where _U.username = ?) { (roles.Contains(UserRoles.Administrator) ? "OR 1 = 1" : string.Empty) })", username);
SqlResult queryResult = new SqlServerCompiler().Compile(query);
return(Convert.ToInt32(
SqlExecutionInstance.ExecuteScalar(new SqlCommand(queryResult.ToString())
{
CommandType = CommandType.Text
},
null,
new SqlConnection(this.connectionString))));
}
public IEnumerable <PointsDataSetHeader> GetDataSetsFiltered(string username, DataSetsFilters filter, string filterValue, int pageIndex, int itemsPerPage)
{
using (var datasetsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetDataSetsFiltered")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("@username", username),
new SqlParameter("@filter_id", (int)filter),
new SqlParameter("@filter_value", filterValue),
new SqlParameter("@page_index", pageIndex),
new SqlParameter("@items_per_page", itemsPerPage)
},
new SqlConnection(this.connectionString)))
{
return(parseDataPointsDataset(datasetsResult.Tables[0].Rows));
}
}
public int GetGeoserverDatasetID(int datasetId)
{
try
{
return(Convert.ToInt32(SqlExecutionInstance.ExecuteScalar(new SqlCommand("GetUserGeoserverPointsDataasetID")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("dataset_id", datasetId)
},
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(-1);
}
}
public int InsertGeoserverColorMap(int geoserverDatasetId, int paletteId)
{
try
{
return(Convert.ToInt32(SqlExecutionInstance.ExecuteScalar(new SqlCommand("InsertGeoserverColorPalette")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("geoserver_data_set_id", geoserverDatasetId),
new SqlParameter("palette_id", paletteId)
},
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(-1);
}
}
public string GetColorMapSerialization(string username, string paletteName)
{
try
{
return(Convert.ToString(SqlExecutionInstance.ExecuteScalar(new SqlCommand("GetUserColorPalette")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("username", username),
new SqlParameter("palette_name", paletteName)
},
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(string.Empty);
}
}
public int CreateUserPointsDataset(string username, string datasetName, PointsSource pointsSource)
{
try
{
return(Convert.ToInt32(SqlExecutionInstance.ExecuteScalar(new SqlCommand("InsertPointsDataset")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("username", username),
new SqlParameter("dataset_name", datasetName),
new SqlParameter("source_name", pointsSource.ToString())
},
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(-1);
}
}
public int RaiseToGeoserverDataset(int datasetId, int?defaultColorPaletteId, string apiUrl)
{
try
{
return(Convert.ToInt32(SqlExecutionInstance.ExecuteScalar(new SqlCommand("InsertGeoserverPointsDataset")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("geoserver_api_url", apiUrl),
new SqlParameter("default_color_palette_id ", defaultColorPaletteId),
new SqlParameter("data_set_id", datasetId)
},
new SqlConnection(this.connectionString))));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(-1);
}
}
private User getUser(string username, string email)
{
using (var userCredentialsInfo = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetUser")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("username", username),
new SqlParameter("email", email)
},
new SqlConnection(this.connectionString)))
{
if (userCredentialsInfo.Tables[0].Rows.Count == 0)
{
return(null);
}
return(this.parseUserDataset(userCredentialsInfo.Tables[0].Rows).FirstOrDefault());
};
}
public bool UpdateDatasetStatus(string datasetName, DatasetStatus status, string username)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("UpdatePointsDatasetStatus")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("datasetName", datasetName),
new SqlParameter("statusId", (int)status),
new SqlParameter("username", username)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
return(true);
}
public IEnumerable <string> GetColorMapsNames(string username)
{
List <string> result = new List <string>();
using (var colorMapsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetUserColorPalettes")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("username", username)
},
new SqlConnection(this.connectionString)))
{
foreach (DataRow row in colorMapsResult.Tables[0].Rows)
{
result.Add((string)row["palette_name"]);
}
}
return(result);
}
public bool RemovePointsDatasetFromUser(string datasetName, string datasetUser, string username)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("RemoveDatapointsFromUser")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("dataset_name", datasetName),
new SqlParameter("dataset_user", datasetUser),
new SqlParameter("username", username),
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
return(true);
}
public bool CheckUser(string username, string password)
{
if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(password))
{
return(false);
}
byte[] storedHashData = new byte[64];
using (var userCredentialsInfo = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetUserPasswordInfo")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("username", username)
},
new SqlConnection(this.connectionString)))
{
if (userCredentialsInfo.Tables[0].Rows.Count == 0)
{
return(false);
}
storedHashData = (byte[])userCredentialsInfo.Tables[0].Rows[0]["hashed_password"];
};
byte[] storedSalt = new byte[32];
byte[] storedHash = new byte[32];
Array.Copy(storedHashData, storedHash, 32);
Array.Copy(storedHashData, 32, storedSalt, 0, 32);
var userTrialPassword = Core.Helper.HashData(Encoding.UTF8.GetBytes(password), storedSalt);
return(storedHash.SequenceEqual(userTrialPassword));
}
public bool UpdateUser(User user)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("UpdateUser")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("username", user.Username),
new SqlParameter("first_name", user.FirstName),
new SqlParameter("last_name", user.LastName),
new SqlParameter("secure_stamp", user.SecurityStamp),
new SqlParameter("email", user.Email)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
return(true);
}
public IList <UserRoles> GetUserRoles(string username)
{
List <UserRoles> result = new List <UserRoles>();
using (var colorMapsResult = SqlExecutionInstance.ExecuteQuery(new SqlCommand("GetUserRoles")
{
CommandType = CommandType.StoredProcedure
},
new SqlParameter[]
{
new SqlParameter("@username", username)
},
new SqlConnection(this.connectionString)))
{
foreach (DataRow row in colorMapsResult.Tables[0].Rows)
{
result.Add(
(UserRoles)Enum.Parse(typeof(UserRoles), (string)row["role_name"])
);
}
}
return(result);
}
public bool UpdateDatasetLimits(string datasetName, string username, decimal?minimumLatitude, decimal?minimumLongitude, decimal?maximumLatitude, decimal?maximumLongitude)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("UpdatePointsDatasetLimits")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("datasetName", datasetName),
new SqlParameter("username", username),
new SqlParameter("minimum_latitude", minimumLatitude),
new SqlParameter("minimum_longitude", minimumLongitude),
new SqlParameter("maximum_latitude", maximumLatitude),
new SqlParameter("maximum_longitude", maximumLongitude)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
return(false);
}
return(true);
}
public bool CreateColorMap(string username, ColorMap colorMap)
{
try
{
SqlExecutionInstance.ExecuteNonQuery(new SqlCommand("InsertColorPalette")
{
CommandType = System.Data.CommandType.StoredProcedure
},
new SqlParameter[] {
new SqlParameter("username", username),
new SqlParameter("palette_name", colorMap.Name),
new SqlParameter("palette_serialization", colorMap.Intervals.JSONSerialize()),
new SqlParameter("main_color_critera", colorMap.MainColorCriteria)
},
new SqlConnection(this.connectionString));
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(false);
}
return(true);
}
public Point GetPointDetails(int dataSetID, PointBase basicPoint)
{
try
{
Query query = new Query(Tables.metadataTableName)
.Select("table_names")
.Where("data_set_id", dataSetID);
SqlResult queryResult = new SqlServerCompiler().Compile(query);
string tableName = SqlExecutionInstance.ExecuteScalar
(new NpgsqlCommand(queryResult.ToString().Replace("[", "").Replace("]", "")),
null,
new NpgsqlConnection(this.connectionString))?.ToString();
if (tableName == null)
{
return(null);
}
query = new Query(Tables.metadataTableName)
.Select("time_references")
.Where("data_set_id", dataSetID);
queryResult = new SqlServerCompiler().Compile(query);
long[] timeReferences = (long[])SqlExecutionInstance.ExecuteScalar
(new NpgsqlCommand(queryResult.ToString().Replace("[", "").Replace("]", "")),
null,
new NpgsqlConnection(this.connectionString));
if (timeReferences == null)
{
return(null);
}
List <string> queryColumns = new List <string>();
for (int i = 0; i < timeReferences.Length; i++)
{
queryColumns.Add($"d_{i}");
}
queryColumns.AddRange(
UserDefinedTypeAttributeExtensions.GetUserDefinedColumnsNames(typeof(Tables.MetadataTableColumns)));
query = new Query(tableName)
.Select(queryColumns.ToArray())
.WhereRaw("geom && ST_Expand(ST_SetSRID(ST_MakePoint(?,?),4326),100)", basicPoint.Longitude, basicPoint.Latitude)
.OrderByRaw("ST_SetSRID(ST_MakePoint(?,?),4326) <-> geom", basicPoint.Longitude, basicPoint.Latitude);
queryResult = new SqlServerCompiler().Compile(query);
using (var datasetResult = SqlExecutionInstance.ExecuteQuery(
new NpgsqlCommand(queryResult.ToString()
.Replace("[", "")
.Replace("]", "")
+ " limit 1"),
null,
new NpgsqlConnection(this.connectionString),
(command) =>
{
return(new NpgsqlDataAdapter((NpgsqlCommand)command));
}))
{
var pointDetails = parsePointDetails(datasetResult.Tables[0].Rows, timeReferences);
if (pointDetails == null)
{
CoreContainers.LogsRepository.LogWarning($"Nothing found for dataset id {dataSetID} - lat: {basicPoint.Latitude}, long: {basicPoint.Longitude}", Core.Database.Logs.LogTrigger.DataAccess);
CoreContainers.LogsRepository.LogWarning($"Query failed for table {tableName}", Core.Database.Logs.LogTrigger.DataAccess);
}
return(pointDetails);
}
}
catch (Exception exception)
{
CoreContainers.LogsRepository.LogError(exception, Core.Database.Logs.LogTrigger.DataAccess);
return(new Point());
}
}
