/// <summary> /// Map a single fishing ground when coming from a selected sampling /// </summary> /// <param name="fishingGround"></param> /// <param name="utmZone"></param> /// <returns></returns> public bool MapFishingGround(string fishingGround, fadUTMZone utmZone, string layerName = "", bool testIfInland = false) { var sf = new Shapefile(); bool success = false; var fgLayerName = "Fishing ground"; if (layerName.Length > 0) { fgLayerName = layerName; } if (sf.CreateNew("", ShpfileType.SHP_POINT)) { var shp = new Shape(); if (shp.Create(ShpfileType.SHP_POINT)) { var iShp = 0; if (_geoProjection.IsGeographic) { var result = FishingGrid.Grid25ToLatLong(fishingGround, utmZone); iShp = shp.AddPoint(result.longitude, result.latitude); } else { FishingGrid.Grid25_to_UTM(fishingGround, out int x, out int y); iShp = shp.AddPoint(x, y); } if (iShp >= 0 && sf.EditInsertShape(shp, 0)) { MapLayersHandler.RemoveLayer(fgLayerName); sf.GeoProjection = _geoProjection; var ifldLabel = sf.EditAddField("Label", FieldType.STRING_FIELD, 1, 15); sf.EditCellValue(ifldLabel, iShp, fgLayerName); sf.CollisionMode = tkCollisionMode.AllowCollisions; SymbolizeFishingGround(sf, fgLayerName, testIfInland); success = MapLayersHandler.AddLayer(sf, fgLayerName, true, true) >= 0; } } } return(success); }
public static Shapefile MapThisGear(string aoiGuid, string gearVarGuid, List <int> samplingYear, bool aggregate = false, bool notInclude1 = false, bool RemoveInland = false) { var query = ""; var dt = new DataTable(); var sf = new Shapefile(); using (var conection = new OleDbConnection(global.ConnectionString)) { try { conection.Open(); var years = ""; foreach (var item in samplingYear) { years += $"{item},"; } years = years.Trim(','); if (aggregate) { if (!notInclude1) { query = $@"SELECT tblSampling.FishingGround, Count(tblSampling.SamplingGUID) AS n FROM tblSampling GROUP BY tblSampling.AOI, tblSampling.GearVarGUID, Year([SamplingDate]), tblSampling.FishingGround HAVING tblSampling.AOI={{{aoiGuid}}} AND tblSampling.GearVarGUID= {{{gearVarGuid}}} AND Year([SamplingDate]) In ({years})"; } else { query = $@"SELECT tblSampling.FishingGround, Count(tblSampling.SamplingGUID) AS n FROM tblSampling GROUP BY tblSampling.FishingGround, tblSampling.AOI, tblSampling.GearVarGUID, Year([SamplingDate]) HAVING Count(tblSampling.SamplingGUID) > 1 AND tblSampling.AOI = {{{aoiGuid}}} AND tblSampling.GearVarGUID = {{{gearVarGuid}}} AND Year([SamplingDate]) In ({years})"; } } else { query = $@"SELECT tblGearClass.GearClassName, tblGearVariations.Variation, tblAOI.AOIName, tblSampling.RefNo, tblSampling.SamplingDate, tblSampling.SamplingTime, tblSampling.FishingGround, tblSampling.TimeSet, tblSampling.DateSet, tblSampling.TimeHauled, tblSampling.DateHauled, tblSampling.NoHauls, tblSampling.NoFishers, tblSampling.Engine, tblSampling.hp, tblSampling.WtCatch, tblSampling.WtSample, tblLandingSites.LSName, temp_VesselType.VesselType FROM tblGearClass INNER JOIN (tblGearVariations INNER JOIN (((tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN tblSampling ON tblLandingSites.LSGUID = tblSampling.LSGUID) INNER JOIN temp_VesselType ON tblSampling.VesType = temp_VesselType.VesselTypeNo) ON tblGearVariations.GearVarGUID = tblSampling.GearVarGUID) ON tblGearClass.GearClass = tblGearVariations.GearClass WHERE tblSampling.AOI= {{{aoiGuid}}} AND tblSampling.GearVarGUID= {{{gearVarGuid}}} AND Year([SamplingDate]) In ({years})"; } var adapter = new OleDbDataAdapter(query, conection); adapter.Fill(dt); var fishingGround = ""; var iShp = 0; var pointAdded = false; var iFldFG = 0; var iFLdCount = 0; fadUTMZone utmZone = FishingGrid.UTMZone; if (sf.CreateNewWithShapeID("", ShpfileType.SHP_POINT)) { sf.GeoProjection = global.MappingForm.GeoProjection; if (aggregate) { iFldFG = sf.EditAddField("fishingGround", FieldType.STRING_FIELD, 1, 9); iFLdCount = sf.EditAddField("n", FieldType.INTEGER_FIELD, 1, 1); } else { } for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; fishingGround = dr["FishingGround"].ToString(); var proceed = false; if (RemoveInland && !FishingGrid.MinorGridIsInland(fishingGround)) { proceed = true; } else if (!RemoveInland) { proceed = true; } if (proceed) { var shp = new Shape(); if (shp.Create(ShpfileType.SHP_POINT)) { if (sf.GeoProjection.IsGeographic) { var result = FishingGrid.Grid25ToLatLong(fishingGround, utmZone); pointAdded = shp.AddPoint(result.longitude, result.latitude) >= 0; } else { } if (pointAdded) { iShp = sf.EditAddShape(shp); if (iShp >= 0) { if (aggregate) { sf.EditCellValue(iFldFG, iShp, fishingGround); sf.EditCellValue(iFLdCount, iShp, (int)dr["n"]); } else { } } } } } } } } catch (Exception ex) { Logger.Log(ex.Message, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name); } } if (sf.NumShapes >= 0) { return(sf); } else { return(null); } }
private void OnbuttonGrid25_Click(object sender, EventArgs e) { bool gridNameAccepted = false; var msg = ""; bool proceed = true; switch (((Button)sender).Name) { case "buttonAdd": if (textBoxColumn.Text.Length > 0 && textBoxGridNo.Text.Length > 0 && textBoxRow.Text.Length > 0) { GridName = textBoxGridNo.Text + "-" + textBoxColumn.Text + textBoxRow.Text; //get lon,lat of grid25 point var pt = FishingGrid.Grid25ToLatLong(GridName, _parent_form.TargetArea.UTMZone); //cofirm if grid25 point is inside fishing ground extent of the target area if (IsInside(pt.longitude, pt.latitude)) { var subgridStyle = FishingGrid.SubGridStyle; //if (FishingGrid.SubGridStyle == fadSubgridSyle.SubgridStyleNone && lvGrids.Items.ContainsKey(GridName)) if (subgridStyle == fadSubgridStyle.SubgridStyleNone && lvGrids.Items.ContainsKey(GridName)) { msg = "Grid name already exists. Please use another"; proceed = false; } //else if (FishingGrid.SubGridStyle != fadSubgridSyle.SubgridStyleNone) else if (subgridStyle != fadSubgridStyle.SubgridStyleNone) { if (textBoxSubGrid.Text.Length > 0) { if (lvGrids.Items.ContainsKey($"{GridName}-{textBoxSubGrid.Text}")) { msg = "Grid name already exists. Please use another"; proceed = false; } } else { if (lvGrids.Items.ContainsKey(GridName)) { msg = "Grid name already exists. Please use another"; proceed = false; } } } if (proceed && FishingGrid.MinorGridIsInland(GridName)) { if (global.MapIsOpen) { global.MappingForm.MapFishingGround(GridName, FishingGrid.UTMZone, GridName, true); DialogResult dr = MessageBox.Show($"{GridName} is located inland\r\n\r\n" + "Accept this location?", "Verify fishing ground location", MessageBoxButtons.YesNo, MessageBoxIcon.Information); if (dr == DialogResult.Yes) { ListViewItem lvi = new ListViewItem(); if (FishingGrid.SubGridStyle != fadSubgridStyle.SubgridStyleNone) { lvi = lvGrids.Items.Add($"{GridName}-{textBoxSubGrid.Text}", $"{GridName}-{textBoxSubGrid.Text}", null); } else { lvi = lvGrids.Items.Add(GridName, GridName, null); } gridNameAccepted = true; lvi.Tag = "new"; textBoxColumn.Text = ""; textBoxRow.Text = ""; textBoxGridNo.Text = ""; textBoxSubGrid.Text = ""; textBoxGridNo.Select(); } else { global.MappingForm.MapLayersHandler.RemoveLayer(GridName); } } else { msg = $"{GridName} is not accepted because it is located inland"; } } else if (proceed) { if (textBoxSubGrid.Text.Length > 0) { GridName += $"-{textBoxSubGrid.Text}"; } if (global.MapIsOpen) { global.MappingForm.MapFishingGround(GridName, FishingGrid.UTMZone, GridName); } if (_selectedItem != null) { lvGrids.Items[_selectedItem.Name].With(o => { o.Text = GridName; o.Name = GridName; _selectedItem = null; }); } else { ListViewItem lvi = new ListViewItem(); //if (FishingGrid.SubGridStyle == fadSubgridSyle.SubgridStyleNone) if (subgridStyle == fadSubgridStyle.SubgridStyleNone) { lvi = lvGrids.Items.Add(GridName, GridName, null); lvi.Tag = "new"; } else { if (textBoxSubGrid.Text.Length == 0) { lvi = lvGrids.Items.Add(GridName, GridName, null); } else { lvi = lvGrids.Items.Add($"{GridName}-{textBoxSubGrid.Text}", $"{GridName}-{textBoxSubGrid.Text}", null); } lvi.Tag = "new"; } } gridNameAccepted = true; textBoxColumn.Text = ""; textBoxRow.Text = ""; textBoxGridNo.Text = ""; textBoxSubGrid.Text = ""; textBoxGridNo.Select(); } } else { msg = "Fishing ground is outside extent"; } } else { msg = "Please fill up all fields"; } if (gridNameAccepted && textBoxSubGrid.Text.Length > 0) { SubGrid = int.Parse(textBoxSubGrid.Text); } break; case "buttonRemove": lvGrids.Items.Remove(_selectedItem); if (global.MapIsOpen) { global.MappingForm.MapLayersHandler.RemoveLayer(_selectedItem.Text); } _selectedItem = null; break; case "buttonRemoveAll": lvGrids.Items.Clear(); _selectedItem = null; break; } if (msg.Length > 0) { MessageBox.Show(msg, "Validation error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
private bool SpeciesOccurenceMapping() { string sql = ""; if (Aggregate) { if (MapInSelectedTargetArea) { sql = $@"SELECT tblAOI.AOIName, tblSampling.FishingGround, Count(tblSampling.SamplingGUID) AS n, tblAOI.UTMZone FROM (tblSampling INNER JOIN tblAOI ON tblSampling.AOI = tblAOI.AOIGuid) INNER JOIN tblCatchComp ON tblSampling.SamplingGUID = tblCatchComp.SamplingGUID WHERE tblCatchComp.NameGUID = {{{ItemToMapGuid}}} AND Year([SamplingDate]) In ({YearsToCSV()}) AND tblSampling.FishingGround Is Not Null AND tblAOI.UTMZone Is Not Null AND tblAOI.AOIGuid={{{SelectedTargetAreaGuid}}} GROUP BY tblAOI.AOIName, tblSampling.FishingGround, tblAOI.UTMZone"; } else { sql = $@"SELECT tblAOI.AOIName, tblSampling.FishingGround, Count(tblSampling.SamplingGUID) AS n, tblAOI.UTMZone FROM (tblSampling INNER JOIN tblCatchComp ON tblSampling.SamplingGUID = tblCatchComp.SamplingGUID) INNER JOIN tblAOI ON tblSampling.AOI = tblAOI.AOIGuid WHERE Year([SamplingDate]) In ({YearsToCSV()}) AND tblSampling.FishingGround Is Not Null AND tblAOI.UTMZone Is Not Null AND tblCatchComp.NameGUID={{{ItemToMapGuid}}} GROUP BY tblAOI.AOIName, tblSampling.FishingGround, tblAOI.UTMZone"; } } else { if (MapInSelectedTargetArea) { sql = $@"SELECT tblAOI.AOIName, tblEnumerators.EnumeratorName, tblSampling.*, tblLandingSites.LSName, tblGearClass.GearClassName, tblGearVariations.Variation, tblAOI.UTMZone FROM tblEnumerators RIGHT JOIN ((tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN ((tblGearClass INNER JOIN tblGearVariations ON tblGearClass.GearClass = tblGearVariations.GearClass) INNER JOIN (tblSampling INNER JOIN tblCatchComp ON tblSampling.SamplingGUID = tblCatchComp.SamplingGUID) ON tblGearVariations.GearVarGUID = tblSampling.GearVarGUID) ON tblLandingSites.LSGUID = tblSampling.LSGUID) ON tblEnumerators.EnumeratorID = tblSampling.Enumerator WHERE tblSampling.AOI={{{SelectedTargetAreaGuid}}} AND tblAOI.UTMZone Is Not Null AND tblCatchComp.NameGUID={{{ItemToMapGuid}}} AND Year([SamplingDate]) In ({YearsToCSV()}) AND tblSampling.FishingGround Is Not Null ORDER BY tblAOI.AOIName, tblGearClass.GearClassName, tblGearVariations.Variation, tblSampling.SamplingDate"; } else { sql = $@"SELECT tblAOI.AOIName, tblEnumerators.EnumeratorName, tblSampling.*, tblLandingSites.LSName, tblGearClass.GearClassName, tblGearVariations.Variation, tblAOI.UTMZone FROM tblEnumerators RIGHT JOIN ((tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN ((tblGearClass INNER JOIN tblGearVariations ON tblGearClass.GearClass = tblGearVariations.GearClass) INNER JOIN (tblSampling INNER JOIN tblCatchComp ON tblSampling.SamplingGUID = tblCatchComp.SamplingGUID) ON tblGearVariations.GearVarGUID = tblSampling.GearVarGUID) ON tblLandingSites.LSGUID = tblSampling.LSGUID) ON tblEnumerators.EnumeratorID = tblSampling.Enumerator WHERE tblCatchComp.NameGUID={{{ItemToMapGuid}}} AND Year([SamplingDate]) In ({YearsToCSV()}) AND tblSampling.FishingGround Is Not Null AND tblAOI.UTMZone Is Not Null ORDER BY tblAOI.AOIName, tblGearClass.GearClassName, tblGearVariations.Variation, tblSampling.SamplingDate"; } } try { var sf = new Shapefile(); if (sf.CreateNew("", ShpfileType.SHP_POINT)) { sf.GeoProjection = MapControl.GeoProjection; var ifldTargetArea = -1; var ifldCount = -1; var ifldFishingGround = -1; var ifldSpeciesName = -1; var ifldGearClass = -1; var ifldGearVariation = -1; var ifldCatchWt = -1; var ifldReferenceNumber = -1; var ifldSamplingDate = -1; var ifldVesselType = -1; var ifldLandindSite = -1; var ifldEnumerator = -1; if (Aggregate) { ifldTargetArea = sf.EditAddField("TargetArea", FieldType.STRING_FIELD, 1, 100); ifldFishingGround = sf.EditAddField("FG", FieldType.STRING_FIELD, 1, 8); ifldSpeciesName = sf.EditAddField("Species", FieldType.STRING_FIELD, 1, 100); ifldCount = sf.EditAddField("n", FieldType.INTEGER_FIELD, 1, 4); } else { ifldReferenceNumber = sf.EditAddField("RefNo", FieldType.STRING_FIELD, 1, 20); ifldEnumerator = sf.EditAddField("Enumerator", FieldType.STRING_FIELD, 1, 30); ifldSpeciesName = sf.EditAddField("Species", FieldType.STRING_FIELD, 1, 100); ifldTargetArea = sf.EditAddField("TargetArea", FieldType.STRING_FIELD, 1, 100); ifldLandindSite = sf.EditAddField("LandingSite", FieldType.STRING_FIELD, 1, 50); ifldSamplingDate = sf.EditAddField("SamplingDate", FieldType.DATE_FIELD, 1, 1); ifldGearClass = sf.EditAddField("GearClass", FieldType.STRING_FIELD, 1, 25); ifldGearVariation = sf.EditAddField("GearVariation", FieldType.STRING_FIELD, 1, 35); ifldFishingGround = sf.EditAddField("FishingGround", FieldType.STRING_FIELD, 1, 8); ifldCatchWt = sf.EditAddField("CatchWeight", FieldType.DATE_FIELD, 2, 10); ifldVesselType = sf.EditAddField("Vessel", FieldType.STRING_FIELD, 1, 25); } using (OleDbConnection conn = new OleDbConnection(global.ConnectionString)) { conn.Open(); var adapter = new OleDbDataAdapter(sql, conn); DataTable dt = new DataTable(); adapter.Fill(dt); if (dt.Rows.Count > 0) { for (int n = 0; n < dt.Rows.Count; n++) { DataRow dr = dt.Rows[n]; var fg = dr["FishingGround"].ToString(); var zone = dr["UTMZone"].ToString(); fadUTMZone utmZone = FishingGrid.ZoneFromZoneName(zone); FishingGrid.UTMZone = utmZone; var latlong = FishingGrid.Grid25ToLatLong(fg, utmZone); var shp = new Shape(); if (shp.Create(ShpfileType.SHP_POINT) && shp.AddPoint(latlong.longitude, latlong.latitude) >= 0) { var iShp = sf.EditAddShape(shp); if (Aggregate) { if (sf.EditCellValue(ifldTargetArea, iShp, dr["AOIName"].ToString())) { sf.EditCellValue(ifldCount, iShp, (int)dr["n"]); sf.EditCellValue(ifldSpeciesName, iShp, ItemName); sf.EditCellValue(ifldFishingGround, iShp, fg); } } else { sf.EditCellValue(ifldSpeciesName, iShp, ItemName); sf.EditCellValue(ifldEnumerator, iShp, dr["EnumeratorName"].ToString()); sf.EditCellValue(ifldTargetArea, iShp, dr["AOIName"].ToString()); sf.EditCellValue(ifldFishingGround, iShp, fg); sf.EditCellValue(ifldGearClass, iShp, dr["GearClassName"].ToString()); sf.EditCellValue(ifldGearVariation, iShp, dr["Variation"].ToString()); sf.EditCellValue(ifldReferenceNumber, iShp, dr["RefNo"].ToString()); sf.EditCellValue(ifldSamplingDate, iShp, string.Format("{0:MMM-dd-yyyy}", (DateTime)dr["SamplingDate"])); sf.EditCellValue(ifldCatchWt, iShp, (double)dr["WtCatch"]); sf.EditCellValue(ifldVesselType, iShp, FishingVessel.VesselTypeFromVesselTypeNumber((int)dr["VesType"])); sf.EditCellValue(ifldLandindSite, iShp, dr["LSName"].ToString()); } } } } } sf.DefaultDrawingOptions.PointShape = tkPointShapeType.ptShapeCircle; ClassificationType classificationType = ClassificationType.None; if (Aggregate) { ShapefileLayerHelper.CategorizeNumericPointLayer(sf, ifldCount); sf.DefaultDrawingOptions.LineVisible = true; sf.DefaultDrawingOptions.LineColor = new Utils().ColorByName(tkMapColor.White); sf.CollisionMode = tkCollisionMode.AllowCollisions; classificationType = ClassificationType.JenksFisher; } else { sf.DefaultDrawingOptions.PointSize = 8; } var h = MapLayersHandler.AddLayer(sf, $"Species mapping: {ItemName} ({YearsToCSV()})", mappingMode: fad3MappingMode.occurenceMappingSpeciesAggregated); MapLayersHandler[h].ClassificationType = classificationType; MapControl.Redraw(); } } catch (Exception ex) { Logger.Log(ex.Message, "OcuurenceMapping", "SpeciesOccurence"); } return(true); }
/// <summary> /// maps fishing grounds belonging to a target area, landing site, or type of gear gear /// </summary> /// <param name="aoiGUID"></param> /// <param name="samplingYears"></param> /// <param name="utmZone"></param> /// <param name="Aggregated"></param> /// <param name="notInclude1"></param> /// <param name="landingSiteGuid"></param> /// <param name="gearVariationGuid"></param> public void MapFishingGrounds(string aoiGUID, string samplingYears, fadUTMZone utmZone, bool Aggregated = true, bool notInclude1 = false, string landingSiteGuid = "", string gearVariationGuid = "") { var query = ""; var sf = new Shapefile(); var ifldAOI = 0; var ifldLS = 0; var ifldGear = 0; var ifldYear = 0; var ifldFG = 0; var ifldCount = 0; var ifldMaxWt = 0; var ifldMinWt = 0; var ifldAfgWt = 0; var ifldEnumerator = 0; var ifldGearClass = 0; var ifldNumberHauls = 0; var ifldNumberFisher = 0; var ifldDateSet = 0; var ifldTimeSet = 0; var ifldDateHauled = 0; var ifldTimeHauled = 0; var ifldVessel = 0; var ifldHP = 0; var ifldCatchWt = 0; if (aoiGUID.Length > 0 && samplingYears.Length > 0 && sf.CreateNewWithShapeID("", ShpfileType.SHP_POINT)) { ifldAOI = sf.EditAddField("AOIName", FieldType.STRING_FIELD, 1, 255); sf.Field[ifldAOI].Alias = "Target area name"; if (Aggregated) { if (landingSiteGuid.Length > 0) { ifldLS = sf.EditAddField("LSName", FieldType.STRING_FIELD, 1, 255); sf.Field[ifldLS].Alias = "Landing site name"; } if (gearVariationGuid.Length > 0) { ifldGear = sf.EditAddField("GearName", FieldType.STRING_FIELD, 1, 255); sf.Field[ifldGear].Alias = "Gear variation used"; } ifldYear = sf.EditAddField("Year", FieldType.INTEGER_FIELD, 1, 4); sf.Field[ifldYear].Alias = "Year sampled"; ifldFG = sf.EditAddField("fg", FieldType.STRING_FIELD, 1, 25); sf.Field[ifldFG].Alias = "Fishing ground"; ifldCount = sf.EditAddField("n", FieldType.INTEGER_FIELD, 1, 4); ifldMaxWt = sf.EditAddField("MaxWt", FieldType.DOUBLE_FIELD, 2, 8); sf.Field[ifldMaxWt].Alias = "Maximum catch weight"; ifldMinWt = sf.EditAddField("MinWt", FieldType.DOUBLE_FIELD, 2, 8); sf.Field[ifldMinWt].Alias = "Minimum catch weight"; ifldAfgWt = sf.EditAddField("AvgWt", FieldType.DOUBLE_FIELD, 2, 8); sf.Field[ifldAfgWt].Alias = "Average catch weight"; if (gearVariationGuid.Length > 0) { query = $@"SELECT tblAOI.AOIName, tblLandingSites.LSName, tblGearVariations.Variation, tblSampling.FishingGround, Year([SamplingDate]) AS samplingYear, Count(tblSampling.SamplingGUID) AS n, Max(tblSampling.WtCatch) AS MaxCatch, Min(tblSampling.WtCatch) AS MinCatch, Avg(tblSampling.WtCatch) AS AvgCatch FROM tblGearVariations INNER JOIN ((tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN tblSampling ON tblLandingSites.LSGUID = tblSampling.LSGUID) ON tblGearVariations.GearVarGUID = tblSampling.GearVarGUID WHERE tblLandingSites.LSGUID={{{landingSiteGuid}}} AND tblSampling.GearVarGUID={{{gearVariationGuid}}} GROUP BY tblAOI.AOIName, tblLandingSites.LSName, tblGearVariations.Variation, tblSampling.FishingGround, Year([SamplingDate]) "; if (notInclude1) { query += $"HAVING Count(tblSampling.SamplingGUID)>1 AND Year([SamplingDate]) In ({samplingYears})"; } else { query += $"HAVING Year([SamplingDate]) In ({samplingYears})"; } } else if (landingSiteGuid.Length > 0) { query = $@"SELECT tblAOI.AOIName, tblLandingSites.LSName, tblSampling.FishingGround, Year([SamplingDate]) AS samplingYear, Count(tblSampling.SamplingGUID) AS n, Max(tblSampling.WtCatch) AS MaxCatch, Min(tblSampling.WtCatch) AS MinCatch, Avg(tblSampling.WtCatch) AS AvgCatch FROM (tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN tblSampling ON tblLandingSites.LSGUID = tblSampling.LSGUID WHERE tblLandingSites.LSGUID={{{landingSiteGuid}}} GROUP BY tblAOI.AOIName, tblLandingSites.LSName, tblSampling.FishingGround, Year([SamplingDate]) "; if (notInclude1) { query += $"HAVING Count(tblSampling.SamplingGUID)>1 AND Year([SamplingDate]) In ({samplingYears})"; } else { query += $"HAVING Year([SamplingDate]) In ({samplingYears})"; } } else { query = $@"SELECT tblAOI.AOIName, tblSampling.FishingGround, Year([SamplingDate]) AS samplingYear, Count(tblSampling.SamplingGUID) AS n, Max(tblSampling.WtCatch) AS MaxCatch, Min(tblSampling.WtCatch) AS MinCatch, Avg(tblSampling.WtCatch) AS AvgCatch FROM tblAOI INNER JOIN tblSampling ON tblAOI.AOIGuid = tblSampling.AOI GROUP BY tblAOI.AOIName, tblSampling.FishingGround, tblSampling.AOI, Year([SamplingDate]) "; if (notInclude1) { query += $"HAVING tblSampling.AOI= {{{aoiGUID}}} AND Count(tblSampling.SamplingGUID)>1 AND Year([SamplingDate]) In ({samplingYears})"; } else { query += $"HAVING tblSampling.AOI= {{{aoiGUID}}} AND Year([SamplingDate]) In ({samplingYears})"; } } } else //not aggregated { ifldEnumerator = sf.EditAddField("Enumerat", FieldType.STRING_FIELD, 1, 100); sf.Field[ifldEnumerator].Alias = "Name of enumerator"; ifldLS = sf.EditAddField("LSName", FieldType.STRING_FIELD, 1, 255); sf.Field[ifldLS].Alias = "Landing site name"; ifldGearClass = sf.EditAddField("GearClass", FieldType.STRING_FIELD, 1, 100); sf.Field[ifldGearClass].Alias = "Gear class used"; ifldGear = sf.EditAddField("GearName", FieldType.STRING_FIELD, 1, 255); sf.Field[ifldGear].Alias = "Gear variation used"; ifldYear = sf.EditAddField("Year", FieldType.INTEGER_FIELD, 1, 4); sf.Field[ifldYear].Alias = "Year sampled"; ifldFG = sf.EditAddField("fg", FieldType.STRING_FIELD, 1, 25); sf.Field[ifldFG].Alias = "Fishing ground"; ifldNumberHauls = sf.EditAddField("NoHauls", FieldType.INTEGER_FIELD, 1, 2); sf.Field[ifldNumberHauls].Alias = "Number of hauls"; ifldNumberFisher = sf.EditAddField("NoFishers", FieldType.INTEGER_FIELD, 1, 2); sf.Field[ifldNumberFisher].Alias = "Number of fishers"; ifldDateSet = sf.EditAddField("DateSet", FieldType.DATE_FIELD, 1, 2); sf.Field[ifldDateSet].Alias = "Date gear set"; ifldTimeSet = sf.EditAddField("TimeSet", FieldType.DATE_FIELD, 1, 2); sf.Field[ifldTimeSet].Alias = "Time gear set"; ifldDateHauled = sf.EditAddField("DateHaul", FieldType.DATE_FIELD, 1, 2); sf.Field[ifldDateHauled].Alias = "Date gear hauled"; ifldTimeHauled = sf.EditAddField("TimeHaul", FieldType.DATE_FIELD, 1, 2); sf.Field[ifldTimeHauled].Alias = "Time gear hauled"; ifldVessel = sf.EditAddField("VesType", FieldType.STRING_FIELD, 1, 30); sf.Field[ifldVessel].Alias = "Type of vessel used"; ifldHP = sf.EditAddField("hp", FieldType.STRING_FIELD, 1, 2); sf.Field[ifldHP].Alias = "Engine hp"; ifldCatchWt = sf.EditAddField("CatchWt", FieldType.DOUBLE_FIELD, 2, 8); sf.Field[ifldCatchWt].Alias = "Catch weight"; query = @"SELECT tblAOI.AOIName, tblEnumerators.EnumeratorName, tblLandingSites.LSName, tblGearClass.GearClassName, tblGearVariations.Variation, Year([SamplingDate]) AS samplingYear, tblSampling.FishingGround, tblSampling.NoHauls, tblSampling.NoFishers, tblSampling.DateSet, tblSampling.TimeSet, tblSampling.DateHauled, tblSampling.TimeHauled, tblSampling.VesType, tblSampling.hp, tblSampling.WtCatch FROM (tblAOI INNER JOIN tblLandingSites ON tblAOI.AOIGuid = tblLandingSites.AOIGuid) INNER JOIN ((tblGearClass INNER JOIN tblGearVariations ON tblGearClass.GearClass = tblGearVariations.GearClass) INNER JOIN (tblEnumerators RIGHT JOIN tblSampling ON tblEnumerators.EnumeratorID = tblSampling.Enumerator) ON tblGearVariations.GearVarGUID = tblSampling.GearVarGUID) ON tblLandingSites.LSGUID = tblSampling.LSGUID "; if (gearVariationGuid.Length > 0) { query += $@"WHERE Year([SamplingDate]) In({samplingYears}) AND tblSampling.LSGUID ={{{landingSiteGuid}}} AND tblSampling.GearVarGUID ={{{gearVariationGuid}}}"; } else if (landingSiteGuid.Length > 0) { query += $"WHERE Year([SamplingDate]) In({samplingYears}) AND tblSampling.LSGUID ={{{landingSiteGuid}}}"; } else { query += $"WHERE Year([SamplingDate]) In({samplingYears}) AND tblSampling.AOI ={{{aoiGUID}}}"; } } using (var conection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;data source=" + global.MDBPath)) { conection.Open(); using (var adapter = new OleDbDataAdapter(query, conection)) using (var myDT = new DataTable()) { adapter.Fill(myDT); //var n = 0; foreach (DataRow dr in myDT.Rows) { var fg = dr["FishingGround"].ToString(); if (fg.Length > 0) { var shp = new MapWinGIS.Shape(); if (shp.Create(ShpfileType.SHP_POINT)) { var proceed = false; if (RemoveInland && !FishingGrid.MinorGridIsInland(fg)) { proceed = true; } else if (!RemoveInland) { proceed = true; } if (proceed) { var iShp = 0; if (_geoProjection.IsGeographic) { var result = FishingGrid.Grid25ToLatLong(fg, utmZone); iShp = shp.AddPoint(result.longitude, result.latitude); } else { FishingGrid.Grid25_to_UTM(fg, out int x, out int y); iShp = shp.AddPoint(x, y); } if (iShp >= 0 && sf.EditInsertShape(shp, 0)) { sf.EditCellValue(ifldAOI, iShp, dr["AOIName"].ToString()); sf.EditCellValue(ifldYear, iShp, int.Parse(dr["samplingYear"].ToString())); sf.EditCellValue(ifldFG, iShp, dr["FishingGround"].ToString()); //aggregated if (Aggregated) { if (landingSiteGuid.Length > 0) { sf.EditCellValue(ifldLS, iShp, dr["LSName"].ToString()); } if (gearVariationGuid.Length > 0) { sf.EditCellValue(ifldGear, iShp, dr["Variation"].ToString()); } sf.EditCellValue(ifldCount, iShp, int.Parse(dr["n"].ToString())); sf.EditCellValue(ifldMaxWt, iShp, double.Parse(dr["MaxCatch"].ToString())); sf.EditCellValue(ifldMinWt, iShp, double.Parse(dr["MinCatch"].ToString())); sf.EditCellValue(ifldAfgWt, iShp, double.Parse(dr["AvgCatch"].ToString())); Console.WriteLine($"n-{sf.CellValue[ifldCount, iShp]}, AvgCatch -{sf.CellValue[ifldAfgWt, iShp]}"); } //not aggregated else { sf.EditCellValue(ifldEnumerator, iShp, dr["EnumeratorName"].ToString()); sf.EditCellValue(ifldLS, iShp, dr["LSName"].ToString()); sf.EditCellValue(ifldGearClass, iShp, dr["GearClassName"].ToString()); sf.EditCellValue(ifldGear, iShp, dr["Variation"].ToString()); if (dr["NoHauls"].ToString().Length > 0) { sf.EditCellValue(ifldNumberHauls, iShp, int.Parse(dr["NoHauls"].ToString())); } if (dr["NoFishers"].ToString().Length > 0) { sf.EditCellValue(ifldNumberFisher, iShp, int.Parse(dr["NoFishers"].ToString())); } if (dr["DateSet"].ToString().Length > 0) { sf.EditCellValue(ifldDateSet, iShp, DateTime.Parse(dr["DateSet"].ToString())); } if (dr["TimeSet"].ToString().Length > 0) { sf.EditCellValue(ifldTimeSet, iShp, DateTime.Parse(dr["TimeSet"].ToString())); } if (dr["DateHauled"].ToString().Length > 0) { sf.EditCellValue(ifldDateHauled, iShp, DateTime.Parse(dr["DateHauled"].ToString())); } if (dr["TimeHauled"].ToString().Length > 0) { sf.EditCellValue(ifldTimeHauled, iShp, DateTime.Parse(dr["TimeHauled"].ToString())); } if (dr["VesType"].ToString().Length > 0) { var vesselType = "Motorized"; switch (int.Parse(dr["VesType"].ToString())) { case 1: vesselType = "Motorized"; break; case 2: vesselType = "Non-motorized"; break; case 3: vesselType = "No vessel used"; break; case 4: vesselType = "Not provided"; break; } sf.EditCellValue(ifldVessel, iShp, vesselType); } if (dr["hp"].ToString().Length > 0) { sf.EditCellValue(ifldHP, iShp, dr["hp"].ToString()); } sf.EditCellValue(ifldCatchWt, iShp, double.Parse(dr["WtCatch"].ToString())); } } } } } //n++; } } } MapLayersHandler.RemoveLayer("Fishing grounds"); if (sf.NumShapes > 0) { sf.GeoProjection = _geoProjection; sf.CollisionMode = tkCollisionMode.AllowCollisions; sf.DefaultDrawingOptions.PointShape = tkPointShapeType.ptShapeCircle; sf.DefaultDrawingOptions.FillColor = new Utils().ColorByName(tkMapColor.Red); sf.DefaultDrawingOptions.LineColor = new Utils().ColorByName(tkMapColor.White); ClassificationType classificationType = ClassificationType.None; if (Aggregated) { classificationType = ClassificationType.NaturalBreaks; ShapefileLayerHelper.CategorizeNumericPointLayer(sf, ifldCount); } else { sf.DefaultDrawingOptions.PointSize = 7; } sf.SelectionAppearance = tkSelectionAppearance.saDrawingOptions; sf.SelectionDrawingOptions.PointShape = tkPointShapeType.ptShapeCircle; if (ShapefileLayerHelper.ExtentsPosition(MapControl.Extents, sf.Extents) == ExtentCompare.excoOutside) { var newExtent = MapControl.Extents; newExtent.MoveTo(sf.Extents.Center.x, sf.Extents.Center.y); MapControl.Extents = newExtent; } var h = MapLayersHandler.AddLayer(sf, "Fishing grounds", true, true); MapLayersHandler[h].ClassificationType = classificationType; } } }
public bool MapSamplingFishingGround(string samplingGuid, fadUTMZone utmZone, string layerName) { var success = false; string refNo = ""; List <string> fg = new List <string>(); DataTable dt = new DataTable(); using (var conection = new OleDbConnection(global.ConnectionString)) { try { conection.Open(); var sql = $"Select RefNo from tblSampling WHERE SamplingGUID = {{{samplingGuid}}}"; var adapter = new OleDbDataAdapter(sql, conection); adapter.Fill(dt); DataRow dr = dt.Rows[0]; refNo = dr["RefNo"].ToString(); sql = $@"SELECT FishingGround FROM tblSampling WHERE SamplingGUID={{{samplingGuid}}} UNION ALL SELECT GridName from tblGrid WHERE SamplingGUID={{{samplingGuid}}}"; dt.Rows.Clear(); adapter = new OleDbDataAdapter(sql, conection); adapter.Fill(dt); for (int i = 0; i < dt.Rows.Count; i++) { dr = dt.Rows[i]; fg.Add(dr["FishingGround"].ToString()); } } catch (Exception ex) { Logger.Log(ex.Message, MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name); } } if (fg.Count > 0) { var sf = new Shapefile(); if (sf.CreateNew("", ShpfileType.SHP_POINT)) { MapLayersHandler.RemoveLayer(layerName); sf.GeoProjection = _geoProjection; var ifldLabel = sf.EditAddField("Label", FieldType.STRING_FIELD, 1, 15); sf.FieldByName["Label"].Alias = "Fishing ground"; var ifldRefNo = sf.EditAddField("RefNo", FieldType.STRING_FIELD, 1, 20); sf.FieldByName["RefNo"].Alias = "Reference number"; foreach (var item in fg) { var shp = new Shape(); if (shp.Create(ShpfileType.SHP_POINT)) { var iShp = 0; var result = FishingGrid.Grid25ToLatLong(item, utmZone); iShp = shp.AddPoint(result.longitude, result.latitude); if (iShp >= 0 && sf.EditInsertShape(shp, 0)) { sf.EditCellValue(ifldLabel, iShp, item); sf.EditCellValue(ifldRefNo, iShp, refNo); sf.CollisionMode = tkCollisionMode.AllowCollisions; } } } sf.DefaultDrawingOptions.SetDefaultPointSymbol(tkDefaultPointSymbol.dpsCircle); sf.DefaultDrawingOptions.FillColor = new Utils().ColorByName(tkMapColor.Red); sf.DefaultDrawingOptions.PointSize = 7; sf.DefaultDrawingOptions.LineVisible = false; success = MapLayersHandler.AddLayer(sf, layerName, true, true) >= 0; if (MapControl == null) { MapControl = global.MappingForm.MapControl; } if (!MapControl.Extents.ToShape().Contains(sf.Extents.ToShape())) { Point pt = sf.Extents.ToShape().Center; Extents ext = MapControl.Extents; ext.MoveTo(pt.x, pt.y); MapControl.Extents = ext; } } } return(success); }