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