public static void SetTreatmentBMPPointInPolygonDataByLocationPoint(this TreatmentBMP treatmentBMP, DbGeometry locationPoint) { treatmentBMP.WatershedID = HttpRequestStorage.DatabaseEntities.Watersheds .FirstOrDefault(x => locationPoint.Intersects(x.WatershedGeometry))?.WatershedID; treatmentBMP.LSPCBasinID = HttpRequestStorage.DatabaseEntities.LSPCBasins .FirstOrDefault(x => locationPoint.Intersects(x.LSPCBasinGeometry))?.LSPCBasinID; treatmentBMP.PrecipitationZoneID = HttpRequestStorage.DatabaseEntities.PrecipitationZones .FirstOrDefault(x => locationPoint.Intersects(x.PrecipitationZoneGeometry))?.PrecipitationZoneID; treatmentBMP.RegionalSubbasinID = HttpRequestStorage.DatabaseEntities.RegionalSubbasins .FirstOrDefault(x => locationPoint.Intersects(x.CatchmentGeometry))?.RegionalSubbasinID; }
public IHttpActionResult SumAllProjectImpact(int start, int count, string prov_code = "", string amphur_code = "", string branch_code = "", string subject_name = "") { try { var _projects = tdmEntities.PROJECT_IMPACT.Where(x => !x.IS_DELETED && x.IS_PUBLISHED).ToList(); var projects = _projects.Where(x => (subject_name == null || x.SUBJECT_NAME.Contains(subject_name)) && (prov_code == null || prov_code.Length == 0 || (( tdaEntities.PROJECT_AREA_47.Any(y => y.SUBJECT_ID.Equals(x.SUBJECT_ID) && y.PROV_CODE.Equals(prov_code)) || tdaEntities.PROJECT_AREA_48.Any(y => y.SUBJECT_ID.Equals(x.SUBJECT_ID) && y.PROV_CODE.Equals(prov_code))))) ).ToList().Select(x => Mapper.Map <PROJECT_IMPACT_ViewModel>(x)).ToList(); List <dynamic> result = new List <dynamic>(); foreach (var c in projects) { List <PROJECT_AREA_ViewModel> projectArea = tdaEntities.PROJECT_AREA_47.Where(x => x.SUBJECT_ID.Equals(c.SUBJECT_ID)).ToList().Select(x => Mapper.Map <PROJECT_AREA_ViewModel>(x)).ToList(); projectArea.AddRange(tdaEntities.PROJECT_AREA_48.Where(x => x.SUBJECT_ID.Equals(c.SUBJECT_ID)).ToList().Select(x => Mapper.Map <PROJECT_AREA_ViewModel>(x)).ToList()); c.PROVINCE = projectArea.GroupBy(x => x.PROV_CODE).Select(x => Mapper.Map <PROVINCE_ViewModel>(tdaEntities.PROVINCEs.Where(y => y.PRO_C.Equals(x.Key)).First())).ToList(); int statusId = projectArea.Count() == 0 ? 1 : (projectArea.Any(x => x.STATUS_PROCESS.Equals("N")) ? 2 : 3); var PROJECT_AREA = projectArea.Select(x => new { x.OBJECTID, x.SUBJECT_ID, x.PROV_CODE, x.STATUS_PROCESS, SHAPE = x.SHAPE.ToString() }).ToList(); DbGeometry PROJECT_AREA_SHAPE = null; if (projectArea.Count() > 0) { PROJECT_AREA_SHAPE = projectArea.ElementAt(0).SHAPE; } for (var i = 1; i < projectArea.Count(); i++) { var temp = PROJECT_AREA_SHAPE.Union(projectArea.ElementAt(i).SHAPE); if (temp != null) { PROJECT_AREA_SHAPE = temp; } } if (statusId == 3) { ////////////////////////////////// if (amphur_code != null && amphur_code.Length == 0) { amphur_code = null; } if (branch_code != null && branch_code.Length == 0) { branch_code = null; } List <PROJECT_PARCEL_47> projectParcel47 = tdaEntities.PROJECT_PARCEL_47.Where(x => x.SUBJECT_ID.Equals(c.SUBJECT_ID) && (amphur_code == null || x.AMPHUR_CODE.Equals(amphur_code)) && (branch_code == null || x.BRANCH_CODE.Equals(branch_code))).ToList(); List <PROJECT_PARCEL_48> projectParcel48 = tdaEntities.PROJECT_PARCEL_48.Where(x => x.SUBJECT_ID.Equals(c.SUBJECT_ID) && (amphur_code == null || x.AMPHUR_CODE.Equals(amphur_code)) && (branch_code == null || x.BRANCH_CODE.Equals(branch_code))).ToList(); List <PARCEL_ViewModel> resultParcel = new List <PARCEL_ViewModel>(); if (projectParcel47.Count() == 0 && projectParcel48.Count() == 0) { //continue; } foreach (var projectParcel in projectParcel47) { var bufferQuery = $@"SELECT [OBJECTID],[OGR_FID],[PARCEL_TYPE],[UTMMAP1],[UTMMAP2],[UTMMAP3],[UTMMAP4],[UTMSCALE],[LAND_NO],[LAND_TH],[LAND_NAME],[ACTION_STATUS],[LAND_AREA],[BRANCH_CODE],[BRANCH_NAME],[CHANGWAT_CODE],[CHANGWAT_NAME],[AMPHUR_CODE],[AMPHUR_NAME],[TUMBON_CODE],[TUMBON_NAME],[CHANOD_NO],[SURVEY_NO],[TABLE_3_SEQ],[ACCOUNTING_PERIOD],[PARCEL_SHAPE],[PARCEL_RN],[STREET_RN],[BLOCK_ZONE_RN],[BLOCK_PRICE_RN],[BLOCK_FIX_RN],[BLOCK_BLUE_RN],[PREV_EVAPRICE],[CURR_EVAPRICE],[DATE_CREATED],[USER_CREATED],[DATE_UPDATED],[USER_UPDATED],[SHAPE],[GDB_GEOMATTR_DATA],[EDIT_FLAG] FROM [TDASSET].[tdadmin].[PARCEL_47_{projectParcel.CHANGWAT_CODE}] WHERE [BRANCH_CODE]='{projectParcel.BRANCH_CODE}' AND [PARCEL_RN]={projectParcel.PARCEL_RN} "; //if (amphur_code != null && amphur_code.Length > 0) //bufferQuery = $@"{bufferQuery} AND [AMPHUR_CODE] = '{amphur_code}'"; bufferQuery = $@"IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'tdadmin' AND TABLE_NAME = 'PARCEL_47_{projectParcel.CHANGWAT_CODE}')) BEGIN {bufferQuery} END"; resultParcel.AddRange(tdaEntities.Database.SqlQuery <PARCEL_ViewModel>(bufferQuery).ToList()); } foreach (var projectParcel in projectParcel48) { var bufferQuery = $@"SELECT [OBJECTID],[OGR_FID],[PARCEL_TYPE],[UTMMAP1],[UTMMAP2],[UTMMAP3],[UTMMAP4],[UTMSCALE],[LAND_NO],[LAND_TH],[LAND_NAME],[ACTION_STATUS],[LAND_AREA],[BRANCH_CODE],[BRANCH_NAME],[CHANGWAT_CODE],[CHANGWAT_NAME],[AMPHUR_CODE],[AMPHUR_NAME],[TUMBON_CODE],[TUMBON_NAME],[CHANOD_NO],[SURVEY_NO],[TABLE_3_SEQ],[ACCOUNTING_PERIOD],[PARCEL_SHAPE],[PARCEL_RN],[STREET_RN],[BLOCK_ZONE_RN],[BLOCK_PRICE_RN],[BLOCK_FIX_RN],[BLOCK_BLUE_RN],[PREV_EVAPRICE],[CURR_EVAPRICE],[DATE_CREATED],[USER_CREATED],[DATE_UPDATED],[USER_UPDATED],[SHAPE],[GDB_GEOMATTR_DATA],[EDIT_FLAG] FROM [TDASSET].[tdadmin].[PARCEL_48_{projectParcel.CHANGWAT_CODE}] WHERE [BRANCH_CODE]={projectParcel.BRANCH_CODE} AND [PARCEL_RN]={projectParcel.PARCEL_RN}"; //if (amphur_code != null && amphur_code.Length > 0) //bufferQuery = $@"{bufferQuery} AND [AMPHUR_CODE] = '{amphur_code}'"; bufferQuery = $@"IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'tdadmin' AND TABLE_NAME = 'PARCEL_47_{projectParcel.CHANGWAT_CODE}')) BEGIN {bufferQuery} END"; resultParcel.AddRange(tdaEntities.Database.SqlQuery <PARCEL_ViewModel>(bufferQuery).ToList()); } DbGeometry PARCEL_SHAPE = null; DbGeometry PARCEL_IMPACT_SHAPE = null; int parcelImpactCount = 0; if (resultParcel.Count() > 0) { PARCEL_SHAPE = resultParcel.ElementAt(0).SHAPE; if (PROJECT_AREA_SHAPE != null && PROJECT_AREA_SHAPE.Intersects(PARCEL_SHAPE)) { PARCEL_IMPACT_SHAPE = PARCEL_SHAPE; parcelImpactCount = 1; } } for (var i = 1; i < resultParcel.Count(); i++) { var SHAPE = resultParcel.ElementAt(i).SHAPE; if (PROJECT_AREA_SHAPE != null && PROJECT_AREA_SHAPE.Intersects(SHAPE)) { var temp2 = PARCEL_IMPACT_SHAPE == null ? SHAPE : PARCEL_IMPACT_SHAPE.Union(SHAPE); if (temp2 != null) { PARCEL_IMPACT_SHAPE = temp2; parcelImpactCount++; } } var temp = PARCEL_SHAPE.Union(SHAPE); if (temp != null) { PARCEL_SHAPE = temp; } } ///////////////////////////////// dynamic project = new { c.ID, c.SUBJECT_ID, c.SUBJECT_NAME, c.PUBLISH_DATE, c.CREATE_DATE, c.CREATE_BY, c.UPDATE_DATE, c.UPDATE_BY, c.PROVINCE, c.IS_PUBLISHED, STATUS = tdmEntities.STATUS_IMPACT.Where(y => y.ID == statusId).First(), PARCEL_COUNT = resultParcel.Count, PARCEL_PRICE = '-', PROJECT_AREA, PARCEL_SHAPE = PARCEL_SHAPE != null?PARCEL_SHAPE.ToString() : null, PROJECT_AREA_SHAPE = PROJECT_AREA_SHAPE != null?PROJECT_AREA_SHAPE.ToString() : null, PARCEL_AREA = resultParcel.Select(x => new SHAPE_ViewModel(x.SHAPE)).ToList(), PARCEL_IMPACT_SHAPE = PARCEL_IMPACT_SHAPE != null?PARCEL_IMPACT_SHAPE.ToString() : null, PARCEL_IMPACT_COUNT = parcelImpactCount }; result.Add(project); } } result = result.Skip(start).Take(count).ToList(); return(Json(result, jsonSetting)); } catch (Exception ex) { return(Json(ex)); } }
private static void CalculateOrganizationAreaOfInterestSubScore(Project project, Organization organization, ref List <double> subScores, ref List <string> matchInsightStrings, ref Dictionary <MatchmakerSubScoreTypeEnum, MatchMakerScoreSubScoreInsight> scoreInsightDictionary) { List <string> localMatchInsights = new List <string>(); // The geometries we use when matching against an Organization are configurable, and may vary, so // here we get them ready to go before trying to match against all the geospatial components. var organizationDbGeometriesToUseInMatching = organization.GetDbGeometriesToUseForMatchMakerMatchingAgainstThisOrganization(); // Simple Location sub-sub-score double simpleLocationSubSubScore = 0.0; { var projectSimpleLocation = project.GetProjectLocationPoint(false); foreach (var currentOrgDbGeometry in organizationDbGeometriesToUseInMatching) { if (projectSimpleLocation != null && projectSimpleLocation.Intersects(currentOrgDbGeometry)) { simpleLocationSubSubScore = 1.0; localMatchInsights.Add($"Simple Location"); // One is enough to score the subscore maximally break; } } } // Detailed location sub-sub-score double detailedLocationSubSubScore = 0.0; { var projectDetailedLocations = project.GetProjectLocationDetailed(false).ToList(); foreach (var currentOrgDbGeometry in organizationDbGeometriesToUseInMatching) { foreach (var currentDetailedLocation in projectDetailedLocations) { DbGeometry currentProjectLocationGeometry = currentDetailedLocation.GetProjectLocationGeometry(); if (currentProjectLocationGeometry.Intersects(currentOrgDbGeometry)) { detailedLocationSubSubScore = 1.0; localMatchInsights.Add($"Detailed Location"); // One is enough to score the subscore maximally break; } } } } // Geospatial area sub-sub-score double projectGeospatialAreaSubSubScore = 0.0; if (MultiTenantHelpers.GetTenantAttributeFromCache().MatchmakerAlgorithmIncludesProjectGeospatialAreas) { var projectGeospatialAreas = project.GetProjectGeospatialAreas().ToList(); foreach (var currentOrgDbGeometry in organizationDbGeometriesToUseInMatching) { foreach (var currentProjectGeoSpatialArea in projectGeospatialAreas) { if (currentProjectGeoSpatialArea.GeospatialAreaFeature.Intersects(currentOrgDbGeometry)) { projectGeospatialAreaSubSubScore = 1.0; localMatchInsights.Add($"{currentProjectGeoSpatialArea.GeospatialAreaName}"); // One is enough to score the subscore maximally break; } } } } // If any of the sub-sub scores are 1.0, the AOI sub score returns 1.0. This could be refined if needed. var allSubScores = new List <double> { simpleLocationSubSubScore, detailedLocationSubSubScore, projectGeospatialAreaSubSubScore }; var areaOfInterestOverallScore = allSubScores.Max(); // We want the overall score to appear first in output //if (areaOfInterestOverallScore > 0) //{ // localMatchInsights.Insert(0, $"Area of Interest SubScore = {areaOfInterestOverallScore:0.0}: "); //} matchInsightStrings.AddRange(localMatchInsights); scoreInsightDictionary.Add(MatchmakerSubScoreTypeEnum.AreaOfInterest, new MatchMakerScoreSubScoreInsight(areaOfInterestOverallScore, localMatchInsights)); CheckEnsureScoreInValidRange(areaOfInterestOverallScore); subScores.Add(areaOfInterestOverallScore); }