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