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