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