/// <summary> /// Get construction year distribution by id. /// </summary> /// <param name="id">Neighborhood identifier.</param> public async Task <ConstructionYearDistribution> GetConstructionYearDistributionByIdAsync(string id) { var sql = @" SELECT -- ConstructionYearDistribution spcy.year_from, spcy.count FROM data.statistics_product_construction_years AS spcy WHERE spcy.neighborhood_id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); List <ConstructionYearPair> pairs = new(); await foreach (var reader in context.EnumerableReaderAsync()) { pairs.Add(new() { Decade = Years.FromDecade(reader.GetInt(0)), TotalCount = reader.GetInt(1) }); } return(new() { Decades = pairs }); }
/// <summary> /// Get foundation type distribution by external id. /// </summary> /// <param name="id">Neighborhood identifier.</param> public async Task <FoundationTypeDistribution> GetFoundationTypeDistributionByExternalIdAsync(string id) { var sql = @" SELECT -- FoundationTypeDistribution spft.foundation_type, round(spft.percentage::numeric, 2) FROM data.statistics_product_foundation_type AS spft JOIN geocoder.neighborhood n ON n.id = spft.neighborhood_id WHERE n.external_id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); List <FoundationTypePair> pairs = new(); await foreach (var reader in context.EnumerableReaderAsync()) { pairs.Add(new() { FoundationType = reader.GetFieldValue <FoundationType>(0), Percentage = reader.GetDecimal(1) }); } return(new() { FoundationTypes = pairs }); }
/// <summary> /// Create new <see cref="User"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="User"/>.</returns> public override async Task <Guid> AddAsync(User entity) { var sql = @" INSERT INTO application.user( given_name, last_name, email, avatar, job_title, phone_number, role) VALUES ( @given_name, @last_name, @email, @avatar, NULLIF(trim(@job_title), ''), REGEXP_REPLACE(@phone_number,'\D','','g'), @role) RETURNING id"; await using var context = await DbContextFactory.CreateAsync(sql); MapToWriter(context, entity); await using var reader = await context.ReaderAsync(); return(reader.GetGuid(0)); }
/// <summary> /// Check if backend is online. /// </summary> /// <remarks> /// Explicit check on result, not all commands are submitted /// to the database. /// </remarks> public async Task <bool> IsAliveAsync() { var sql = @"SELECT 1"; await using var context = await DbContextFactory.CreateAsync(sql); return(await context.ScalarAsync <int>() == 1); }
/// <summary> /// Gets the risk index by its internal building id. /// </summary> /// <param name="id">Internal building id.</param> public async Task <bool> GetRiskIndexAsync(string id) { var sql = @" WITH identifier AS ( SELECT type, id FROM geocoder.id_parser(@id) ), tracker AS ( INSERT INTO application.product_tracker AS pt (organization_id, product, building_id) SELECT @tenant, 'riskindex', ac.building_id FROM data.analysis_complete ac, identifier WHERE CASE WHEN identifier.type = 'fundermaps' THEN ac.building_id = identifier.id WHEN identifier.type = 'nl_bag_address' THEN ac.address_external_id = identifier.id WHEN identifier.type = 'nl_bag_building' THEN ac.external_building_id = identifier.id WHEN identifier.type = 'nl_bag_berth' THEN ac.external_building_id = identifier.id WHEN identifier.type = 'nl_bag_posting' THEN ac.external_building_id = identifier.id END LIMIT 1 RETURNING pt.building_id ) SELECT -- AnalysisComplete 'a'::data.foundation_risk_indication <> ANY (ARRAY[ CASE WHEN ac.drystand_risk IS NULL THEN 'a'::data.foundation_risk_indication ELSE ac.drystand_risk END, CASE WHEN ac.bio_infection_risk IS NULL THEN 'a'::data.foundation_risk_indication ELSE ac.bio_infection_risk END, CASE WHEN ac.dewatering_depth_risk IS NULL THEN 'a'::data.foundation_risk_indication ELSE ac.dewatering_depth_risk END, CASE WHEN ac.unclassified_risk IS NULL THEN 'a'::data.foundation_risk_indication ELSE ac.unclassified_risk END]) AS has_risk FROM data.analysis_complete ac, tracker WHERE ac.building_id = tracker.building_id LIMIT 1"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); context.AddParameterWithValue("tenant", AppContext.TenantId); return(await context.ScalarAsync <bool>()); }
/// <summary> /// Retrieve number of entities. /// </summary> /// <returns>Number of entities.</returns> public override async Task <long> CountAsync() { var sql = @" SELECT COUNT(*) FROM report.incident"; await using var context = await DbContextFactory.CreateAsync(sql); return(await context.ScalarAsync <long>()); }
/// <summary> /// Retrieve number of entities. /// </summary> /// <returns>Number of entities.</returns> public override async Task <long> CountAsync() { var sql = @" SELECT COUNT(*) FROM application.organization_proposal"; await using var context = await DbContextFactory.CreateAsync(sql); return(await context.ScalarAsync <long>()); }
/// <summary> /// Create new <see cref="InquiryFull"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="InquiryFull"/>.</returns> public override async Task <int> AddAsync(InquiryFull entity) { var sql = @" WITH attribution AS ( INSERT INTO application.attribution( reviewer, creator, owner, contractor) VALUES ( @reviewer, @user, @tenant, @contractor) RETURNING id AS attribution_id ) INSERT INTO report.inquiry( document_name, inspection, joint_measurement, floor_measurement, note, document_date, document_file, attribution, access_policy, type, standard_f3o) SELECT @document_name, @inspection, @joint_measurement, @floor_measurement, NULLIF(trim(@note), ''), @document_date, @document_file, attribution_id, @access_policy, @type, @standard_f3o FROM attribution RETURNING id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("reviewer", entity.Attribution.Reviewer); context.AddParameterWithValue("user", AppContext.UserId); context.AddParameterWithValue("tenant", AppContext.TenantId); context.AddParameterWithValue("contractor", entity.Attribution.Contractor); MapToWriter(context, entity); return(await context.ScalarAsync <int>()); }
/// <summary> /// Create new <see cref="Incident"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="Incident"/>.</returns> public override async Task <string> AddAsync(Incident entity) { var sql = @" INSERT INTO report.incident( id, foundation_type, chained_building, owner, foundation_recovery, neightbor_recovery, foundation_damage_cause, document_file, note, internal_note, contact, foundation_damage_characteristics, environment_damage_characteristics, address, audit_status, question_type, meta) VALUES ( report.fir_generate_id(@client_id), @foundation_type, @chained_building, @owner, @foundation_recovery, @neightbor_recovery, @foundation_damage_cause, NULLIF(@document_file, '{}'::text[]), NULLIF(trim(@note), ''), NULLIF(trim(@internal_note), ''), NULLIF(trim(@email), ''), NULLIF(@foundation_damage_characteristics, '{}'::report.foundation_damage_characteristics[]), NULLIF(@environment_damage_characteristics, '{}'::report.environment_damage_characteristics[]), @address, @audit_status, @question_type, @meta) RETURNING id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("client_id", entity.ClientId); MapToWriter(context, entity); await using var reader = await context.ReaderAsync(); return(reader.GetString(0)); }
/// <summary> /// Retrieve number of entities. /// </summary> /// <returns>Number of entities.</returns> public override async Task <long> CountAsync() { var sql = @" SELECT COUNT(*) FROM report.inquiry AS i JOIN application.attribution AS a ON a.id = i.attribution WHERE a.owner = @tenant" ; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("tenant", AppContext.TenantId); return(await context.ScalarAsync <long>()); }
public async Task SetOrganizationRoleByUserIdAsync(Guid userId, OrganizationRole role) { var sql = @" UPDATE application.organization_user SET role = @role WHERE user_id = @user_id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("user_id", userId); context.AddParameterWithValue("role", role); await context.NonQueryAsync(); }
/// <summary> /// Delete <see cref="OrganizationProposal"/>. /// </summary> /// <param name="id">Entity id.</param> public override async Task DeleteAsync(Guid id) { ResetCacheEntity(id); var sql = @" DELETE FROM application.organization_proposal WHERE id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); await context.NonQueryAsync(); }
/// <summary> /// Delete <see cref="Incident"/>. /// </summary> /// <param name="id">Entity identifier.</param> public override async Task DeleteAsync(string id) { ResetCacheEntity(id); var sql = @" DELETE FROM report.incident WHERE id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); await context.NonQueryAsync(); }
public async Task <OrganizationRole> GetOrganizationRoleByUserIdAsync(Guid userId) { var sql = @" SELECT role FROM application.organization_user WHERE user_id = @user_id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("user_id", userId); await using var reader = await context.ReaderAsync(); return(reader.GetFieldValue <OrganizationRole>(0)); }
/// <summary> /// Delete <see cref="Contact"/>. /// </summary> /// <param name="email">Entity email.</param> public override async Task DeleteAsync(string email) { ResetCacheEntity(email); var sql = @" DELETE FROM application.contact WHERE email = @email"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("email", email); await context.NonQueryAsync(); }
/// <summary> /// Get data collection percentage by id. /// </summary> /// <param name="id">Neighborhood identifier.</param> public async Task <decimal> GetDataCollectedPercentageByIdAsync(string id) { var sql = @" SELECT -- DataCollected round(spdc.percentage::numeric, 2) FROM data.statistics_product_data_collected AS spdc WHERE spdc.neighborhood_id = @id LIMIT 1"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); return(await context.ScalarAsync <decimal>(resultGuard : false)); }
/// <summary> /// Get foundation risk distribution by id. /// </summary> /// <param name="id">Neighborhood identifier.</param> public async Task <FoundationRiskDistribution> GetFoundationRiskDistributionByIdAsync(string id) { var sql = @" SELECT -- FoundationRiskDistribution spfr.foundation_risk, round(spfr.percentage::numeric, 2) FROM data.statistics_product_foundation_risk AS spfr WHERE spfr.neighborhood_id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); Dictionary <FoundationRisk, decimal> map = new() { { FoundationRisk.A, 0 }, { FoundationRisk.B, 0 },
/// <summary> /// Gets an analysis product by its external building id and source. /// </summary> /// <param name="id">External building id.</param> public async Task <AnalysisProduct2> GetByExternalId2Async(string id) { var sql = @" WITH tracker AS ( INSERT INTO application.product_tracker AS pt (organization_id, product, building_id) SELECT @tenant, 'analysis2', ac.building_id FROM data.analysis_complete ac WHERE ac.external_building_id = upper(@external_id) LIMIT 1 returning pt.building_id ) SELECT -- AnalysisComplete ac.building_id, ac.external_building_id, ac.address_id, ac.address_external_id, ac.neighborhood_id, ac.construction_year, ac.foundation_type, ac.foundation_type_reliability, ac.restoration_costs, ac.drystand_risk, ac.drystand_risk_reliability, ac.bio_infection_risk, ac.bio_infection_risk_reliability, ac.dewatering_depth_risk, ac.dewatering_depth_risk_reliability, ac.unclassified_risk, ac.recovery_type FROM data.analysis_complete ac, tracker WHERE ac.building_id = tracker.building_id LIMIT 1"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("external_id", id); context.AddParameterWithValue("user", AppContext.UserId); context.AddParameterWithValue("tenant", AppContext.TenantId); await using var reader = await context.ReaderAsync(); return(MapFromReader2(reader)); }
/// <summary> /// Retrieve number of entities. /// </summary> /// <returns>Number of entities.</returns> public async Task <long> CountAsync(int report) { var sql = @" SELECT COUNT(*) FROM report.inquiry_sample AS s JOIN report.inquiry AS i ON i.id = s.inquiry JOIN application.attribution AS a ON a.id = i.attribution WHERE a.owner = @tenant AND i.id = @id"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", report); context.AddParameterWithValue("tenant", AppContext.TenantId); return(await context.ScalarAsync <long>()); }
public async Task <Guid> AddFromProposalAsync(Guid id, string email, string passwordHash) { var sql = @" SELECT application.create_organization( @id, @email, @passwordHash)"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); context.AddParameterWithValue("email", email); context.AddParameterWithValue("passwordHash", passwordHash); await using var reader = await context.ReaderAsync(); return(reader.GetGuid(0)); }
// TODO: This should not be necessary public async Task <bool> IsUserInOrganization(Guid organizationId, Guid userId) { // FUTURE: database function var sql = @" SELECT EXISTS ( SELECT 1 FROM application.organization_user WHERE user_id = @user_id AND organization_id = @organization_id LIMIT 1 )"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("user_id", userId); context.AddParameterWithValue("organization_id", organizationId); return(await context.ScalarAsync <bool>()); }
/// <summary> /// Retrieve all users by organization. /// </summary> /// <returns>List of user identifiers.</returns> public async IAsyncEnumerable <Guid> ListAllAsync(Guid organizationId, Navigation navigation) { var sql = @" SELECT user_id FROM application.organization_user WHERE organization_id = @organization_id"; // TODO: // sql = ConstructNavigation(sql, navigation); await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("organization_id", organizationId); await foreach (var reader in context.EnumerableReaderAsync()) { yield return(reader.GetGuid(0)); } }
/// <summary> /// Delete <see cref="InquiryFull"/>. /// </summary> /// <param name="id">Entity object.</param> public override async Task DeleteAsync(int id) { ResetCacheEntity(id); var sql = @" DELETE FROM report.inquiry AS i USING application.attribution AS a WHERE a.id = i.attribution AND i.id = @id AND a.owner = @tenant"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); context.AddParameterWithValue("tenant", AppContext.TenantId); await context.NonQueryAsync(); }
public async Task AddAsync(Guid organizationId, Guid userId, OrganizationRole role) { var sql = @" INSERT INTO application.organization_user( user_id, organization_id, role) VALUES ( @user_id, @organization_id, @role)"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("user_id", userId); context.AddParameterWithValue("organization_id", organizationId); context.AddParameterWithValue("role", role); await context.NonQueryAsync(); }
// FUTURE: Email should also be normalized. /// <summary> /// Create new <see cref="Contact"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="Contact"/>.</returns> public override async Task <string> AddAsync(Contact entity) { var sql = @" INSERT INTO application.contact( email, name, phone_number) VALUES ( @email, NULLIF(trim(@name), ''), NULLIF(trim(@phone_number), '')) ON CONFLICT DO NOTHING"; await using var context = await DbContextFactory.CreateAsync(sql); MapToWriter(context, entity); await context.NonQueryAsync(affectedGuard : false); return(entity.Email); }
/// <summary> /// Create new <see cref="OrganizationProposal"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="OrganizationProposal"/>.</returns> public override async Task <Guid> AddAsync(OrganizationProposal entity) { if (entity is null) { throw new ArgumentNullException(nameof(entity)); } // FUTURE: Call SP directly var sql = @" SELECT application.create_organization_proposal( @name, @email)"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("name", entity.Name); context.AddParameterWithValue("email", entity.Email); await using var reader = await context.ReaderAsync(); return(reader.GetGuid(0)); }
/// <summary> /// Retrieve all product telemetrics. /// </summary> public async IAsyncEnumerable <ProductTelemetry> ListAllUsageAsync() { var sql = @" SELECT -- ProductTracker pt.product, count(pt.organization_id) FROM application.product_tracker AS pt WHERE pt.organization_id = @tenant GROUP BY pt.organization_id, pt.product"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("tenant", AppContext.TenantId); await foreach (var reader in context.EnumerableReaderAsync()) { yield return(new() { Product = reader.GetString(0), Count = reader.GetInt(1), }); } }
/// <summary> /// Gets an analysis product by its internal building id. /// </summary> /// <param name="id">Internal building id.</param> public async Task <AnalysisProduct3> Get3Async(string id) { var sql = @" WITH identifier AS ( SELECT type, id FROM geocoder.id_parser(@id) LIMIT 1 ), tracker AS ( INSERT INTO application.product_tracker AS pt (organization_id, product, building_id) SELECT @tenant, 'analysis3', CASE WHEN identifier.type = 'fundermaps' THEN ( SELECT-- AnalysisComplete ac.building_id FROM data.analysis_complete ac WHERE ac.building_id = identifier.id LIMIT 1 ) WHEN identifier.type = 'nl_bag_building' OR identifier.type = 'nl_bag_berth' OR identifier.type = 'nl_bag_posting' THEN ( SELECT-- AnalysisComplete ac.building_id FROM data.analysis_complete ac WHERE ac.external_building_id = identifier.id LIMIT 1 ) WHEN identifier.type = 'nl_bag_address' THEN ( SELECT-- AnalysisComplete ac.building_id FROM data.analysis_complete ac WHERE ac.address_external_id = identifier.id LIMIT 1 ) END FROM identifier LIMIT 1 RETURNING building_id ) SELECT-- AnalysisComplete ac.building_id, ac.external_building_id, ac.address_id, ac.address_external_id, ac.neighborhood_id, ac.construction_year, ac.construction_year_reliability, ac.foundation_type, ac.foundation_type_reliability, ac.restoration_costs, ac.height, ac.velocity, ac.ground_water_level, ac.ground_level, ac.soil, ac.surface_area, ac.damage_cause, ac.enforcement_term, ac.overall_quality, ac.inquiry_type, ac.drystand, ac.drystand_risk, ac.drystand_risk_reliability, ac.bio_infection_risk, ac.bio_infection_risk_reliability, ac.dewatering_depth, ac.dewatering_depth_risk, ac.dewatering_depth_risk_reliability, ac.unclassified_risk, ac.recovery_type FROM data.analysis_complete ac, tracker WHERE ac.building_id = tracker.building_id LIMIT 1"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("id", id); context.AddParameterWithValue("tenant", AppContext.TenantId); await using var reader = await context.ReaderAsync(); return(MapFromReader3(reader)); }
/// <summary> /// Gets an analysis product by its external address id and source. /// </summary> /// <param name="id">External address id.</param> public async Task <AnalysisProduct> GetByAddressExternalIdAsync(string id) { var sql = @" WITH tracker AS ( INSERT INTO application.product_tracker AS pt (organization_id, product, building_id) SELECT @tenant, 'analysis', aa.id FROM data.analysis_address AS aa WHERE aa.address_external_id = upper(@external_id) LIMIT 1 returning pt.building_id ) SELECT -- AnalysisAddress aa.id, aa.external_id, aa.external_source, aa.construction_year, aa.construction_year_source, aa.address_id, aa.address_external_id, aa.postal_code, aa.neighborhood_id, aa.groundwater_level, aa.soil, aa.building_height, aa.ground_level, aa.cpt, aa.monitoring_well, aa.recovery_advised, aa.damage_cause, aa.substructure, aa.document_name, aa.document_date, aa.inquiry_type, aa.recovery_type, aa.recovery_status, aa.surface_area, aa.living_area, aa.foundation_bearing_layer, aa.restoration_costs, aa.foundation_type, aa.foundation_type_reliability, aa.drystand, aa.drystand_reliability, aa.drystand_risk, aa.dewatering_depth, aa.dewatering_depth_reliability, aa.dewatering_depth_risk, aa.bio_infection, aa.bio_infection_reliability, aa.bio_infection_risk FROM data.analysis_address AS aa, tracker WHERE aa.id = tracker.building_id LIMIT 1"; await using var context = await DbContextFactory.CreateAsync(sql); context.AddParameterWithValue("external_id", id); context.AddParameterWithValue("tenant", AppContext.TenantId); await using var reader = await context.ReaderAsync(); return(MapFromReader(reader)); }
/// <summary> /// Create new <see cref="InquirySample"/>. /// </summary> /// <param name="entity">Entity object.</param> /// <returns>Created <see cref="InquirySample"/>.</returns> public override async Task <int> AddAsync(InquirySample entity) { var sql = @" INSERT INTO report.inquiry_sample( inquiry, address, note, built_year, substructure, overall_quality, wood_quality, construction_quality, wood_capacity_horizontal_quality, pile_wood_capacity_vertical_quality, carrying_capacity_quality, mason_quality, wood_quality_necessity, construction_level, wood_level, pile_diameter_top, pile_diameter_bottom, pile_head_level, pile_tip_level, foundation_depth, mason_level, concrete_charger_length, pile_distance_length, wood_penetration_depth, cpt, monitoring_well, groundwater_level_temp, groundlevel, groundwater_level_net, foundation_type, enforcement_term, recovery_advised, damage_cause, damage_characteristics, construction_pile, wood_type, wood_encroachement, crack_indoor_restored, crack_indoor_type, crack_indoor_size, crack_facade_front_restored, crack_facade_front_type, crack_facade_front_size, crack_facade_back_restored, crack_facade_back_type, crack_facade_back_size, crack_facade_left_restored, crack_facade_left_type, crack_facade_left_size, crack_facade_right_restored, crack_facade_right_type, crack_facade_right_size, deformed_facade, threshold_updown_skewed, threshold_front_level, threshold_back_level, skewed_parallel, skewed_perpendicular, skewed_facade, settlement_speed, skewed_window_frame) VALUES ( @inquiry, @address, @note, @built_year, @substructure, @overall_quality, @wood_quality, @construction_quality, @wood_capacity_horizontal_quality, @pile_wood_capacity_vertical_quality, @carrying_capacity_quality, @mason_quality, @wood_quality_necessity, @construction_level, @wood_level, @pile_diameter_top, @pile_diameter_bottom, @pile_head_level, @pile_tip_level, @foundation_depth, @mason_level, @concrete_charger_length, @pile_distance_length, @wood_penetration_depth, @cpt, @monitoring_well, @groundwater_level_temp, @groundlevel, @groundwater_level_net, @foundation_type, @enforcement_term, @recovery_advised, @damage_cause, @damage_characteristics, @construction_pile, @wood_type, @wood_encroachement, @crack_indoor_restored, @crack_indoor_type, @crack_indoor_size, @crack_facade_front_restored, @crack_facade_front_type, @crack_facade_front_size, @crack_facade_back_restored, @crack_facade_back_type, @crack_facade_back_size, @crack_facade_left_restored, @crack_facade_left_type, @crack_facade_left_size, @crack_facade_right_restored, @crack_facade_right_type, @crack_facade_right_size, @deformed_facade, @threshold_updown_skewed, @threshold_front_level, @threshold_back_level, @skewed_parallel, @skewed_perpendicular, @skewed_facade, @settlement_speed, @skewed_window_frame) RETURNING id"; await using var context = await DbContextFactory.CreateAsync(sql); MapToWriter(context, entity); return(await context.ScalarAsync <int>()); }