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 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());
}
}
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 <PointsDataSetHeader> GetDataSetsFiltered(string username, bool ignoreUsername, IEnumerable <Tuple <DataSetsFilters, string> > filters, int pageIndex, int itemsPerPage)
{
IList <UserRoles> roles = this.GetUserRoles(username);
Query query = new Query("dbo.DataSets as DS")
.Select(new string[] {
"U.username",
"DS.dataset_name",
"DS.data_set_id as dataset_id",
"DS.status_id",
"DS.source_name"
})
.Join("dbo.Users as U", "DS.user_id", "U.user_id")
.LeftJoin("dbo.UsersAllowedDatasets as UAD", "UAD.dataset_id", "DS.data_set_id");
Func <DataSetsFilters, string> getColumnName = (filter) =>
{
switch (filter)
{
case DataSetsFilters.DataSetName:
return("DS.dataset_name");
case DataSetsFilters.Username:
return("U.username");
case DataSetsFilters.Source:
return("DS.source_name");
case DataSetsFilters.IsDemo:
return("DS.is_demo");
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.WhereRaw($"(UAD.user_id = (select top 1 user_id from Users as _U where _U.username = ?) { (roles.Contains(UserRoles.Administrator) || ignoreUsername ? "OR 1 = 1" : string.Empty) })", username);
query = query.OrderByDesc("DS.data_set_id")
.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(parseDataPointsDataset(colorMapsResult.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));
}
}