public ActionResult GetClusterMapData() { var db = new ApplicationDbContext(); var measureService = new MeasureService(db); var listMarker = new List <Marker>(); db.ProductionUnits.Include(p => p.owner.preferedMoment).ToList().ForEach(p => { var phone = p.owner.phoneNumber == null ? "00 33 3 67 37 00 56" : p.owner.phoneNumber; var contactMail = p.owner.contactMail == null ? "*****@*****.**" : p.owner.contactMail; listMarker.Add(new Marker(p.locationLatitude, p.locationLongitude, String.Format(@"Click for view details")) { shape = "redMarker" }); }); var map = new Models.ClusterMap() { Name = "map", CenterLatitude = 44.0235561, CenterLongitude = 0.3640063, Zoom = 4, TileUrlTemplate = "http://#= subdomain #.tile.openstreetmap.org/#= zoom #/#= x #/#= y #.png", TileSubdomains = new string[] { "a", "b", "c" }, TileAttribution = "© <a href='http://osm.org/copyright'>OpenStreetMap contributors</a>" }; var result = new GeoClusterMapData { type = "FeatureCollection" }; var list = new List <GeoFeature>(); foreach (var marker in listMarker) { var feature = new GeoFeature { type = "Feature" }; feature.properties = new GeoProperties { scalerank = 2, name = marker.name, long_x = marker.latlng[0], lat_y = marker.latlng[1] }; feature.geometry = new Geometry { type = "Point", coordinates = new double[] { marker.latlng[1], marker.latlng[0] } }; list.Add(feature); } result.features = list.ToArray(); map.ClusterData = result; var data = new JavaScriptSerializer().Serialize(map); return(Json(new { data }, JsonRequestBehavior.AllowGet)); }
public ActionResult GetClusterMapData() { var db = new ApplicationDbContext(); var measureService = new MeasureService(db); var listMarker = new List <Marker>(); db.ProductionUnits.ToList().ForEach(p => listMarker.Add(new Marker(p.locationLatitude, p.locationLongitude, String.Format("{0} </br> start since {1}", p.info, p.startDate.ToShortDateString())) { shape = "redMarker" })); var map = new Models.ClusterMap() { Name = "map", CenterLatitude = 44.0235561, CenterLongitude = -10.3640063, Zoom = 4, TileUrlTemplate = "http://#= subdomain #.tile.openstreetmap.org/#= zoom #/#= x #/#= y #.png", TileSubdomains = new string[] { "a", "b", "c" }, TileAttribution = "© <a href='http://osm.org/copyright'>OpenStreetMap contributors</a>" }; var result = new GeoClusterMapData { type = "FeatureCollection" }; var list = new List <GeoFeature>(); foreach (var marker in listMarker) { var feature = new GeoFeature { type = "Feature" }; feature.properties = new GeoProperties { scalerank = 2, name = marker.name, long_x = marker.latlng[0], lat_y = marker.latlng[1] }; feature.geometry = new Geometry { type = "Point", coordinates = new double[] { marker.latlng[1], marker.latlng[0] } }; list.Add(feature); } result.features = list.ToArray(); map.ClusterData = result; var data = new JavaScriptSerializer().Serialize(map); return(Json(new { data }, JsonRequestBehavior.AllowGet)); }
/// <summary> /// 根据对象ID、G|P|B查询对象3GX数据,3GX数据中可包含坐标信息或参数信息或两者都包含 /// </summary> /// <param name="boid">对象ID</param> /// <param name="category">枚举值[G|P|B]</param> /// <returns></returns> public System.Xml.XmlDocument Get3GXById(string boid, GGGXDataCategory category) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT T.BOID,T.NAME,T1.BOT,T1.FT FROM BO T "); strSql.Append(" LEFT JOIN OBJECTTYPE T1 "); strSql.Append(" ON T.BOTID=T1.BOTID "); strSql.Append(" WHERE T.BOID =@BOID "); SqlParameter[] parameters = { new SqlParameter("BOID", SqlDbType.VarChar, 36) }; parameters[0].Value = boid; DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters); List <GeoFeature> ftList = new List <GeoFeature>(); foreach (DataRow row in dt.Rows) { GeoFeature ft = new GeoFeature(); ft.BOID = row["BOID"].ToString(); ft.BOT = row["BOT"].ToString(); ft.FT = row["FT"].ToString(); ft.NAME = row["NAME"].ToString(); ft.AliasNameList = Comm.GetAliasNameByBoid(ft.BOID); if (category == GGGXDataCategory.B) { ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); } else if (category == GGGXDataCategory.P) { ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = null; } else if (category == GGGXDataCategory.G) { ft.PropertyList = null; ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); } ftList.Add(ft); } return(GGGXParse.ConvertFT.FeatureToGGGX(ftList)); }
public IActionResult Index() { //CleaningView model; //model = new CleaningView(); //tijdelijke model inhoud // implemetn get cleaning list logic; //model = new CleaningView(); //dynamic model = new ExpandoObject(); CleaningMasterView Model = new CleaningMasterView(); Model.cleanings = maintenanceLogic.GetCleaningList(); Model.cleaning = new CleaningView(); GeoFeature geo = new GeoFeature(); Model.geo = geo; return(View(Model)); }
/// <summary> /// 保存3GX数据到数据库中 /// </summary> /// <param name="boExModelList"></param> /// <param name="replaceOrLeave"></param> /// <returns></returns> private int Save3GX(List <GeoFeature> ftList, string replaceOrLeave = null) { int i = 0; int iInsertCount = 0; //记录添加条数 int iUpdateCount = 0; //记录修改条数 bool IsInsert = true; OracleDBHelper.SQLEntity sqlEntity; OracleParameter[] parameters; StringBuilder strSql = new StringBuilder(); foreach (GeoFeature bo in ftList) { subErr = new SubmissionError(); List <OracleDBHelper.SQLEntity> sqlList = new List <OracleDBHelper.SQLEntity>(); bool isBoExist = false; GeoFeature seachFt = GetBoListByName(bo.NAME, bo.FT); if (seachFt != null) { isBoExist = true; //对象存在则需要把带入库的对象id都换成库中的该对象id bo.BOID = seachFt.BOID; } else { bo.BOID = System.Guid.NewGuid().ToString(); } #region 添加对象 //存在且保留 if (isBoExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } //覆盖 else { strSql = new StringBuilder(); //对象不存在 if (!isBoExist) { strSql.Append(" INSERT INTO BO( "); strSql.Append(" NAME,BOID,BOTID) "); strSql.Append(" VALUES (:NAME,:BOID,:BOTID)"); IsInsert = true; } //对象存在 else { strSql.Append(" UPDATE BO SET "); strSql.Append(" NAME=:NAME "); strSql.Append(" WHERE BOID=:BOID AND BOTID= :BOTID "); IsInsert = false; } parameters = new OracleParameter[] { new OracleParameter("NAME", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("BOTID", OracleDbType.Varchar2, 36) }; parameters[0].Value = bo.NAME; parameters[1].Value = bo.BOID == null?System.Guid.NewGuid().ToString() : bo.BOID; //根据FT查找对象的BOTID没用找到时,添加会出现异常 parameters[2].Value = DBUtility.OracleDBHelper.OracleHelper.ExecuteQueryText <string>(string.Format(" SELECT BOTID FROM OBJECTTYPE WHERE FT='{0}'", bo.FT)).FirstOrDefault(); if (parameters[2].Value == null) { subErr.BOName = bo.NAME; subErr.Error = bo.NAME + "对应的对象类型不存在"; result.TotalBO = ftList.Count; result.UpdatedBO = iUpdateCount; result.FailedBO = ListsubErr.Count + 1; result.InsertedBO = iInsertCount; result.Errors.Add(subErr); continue; } sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } #endregion #region 添加坐标数据 bool isGeometryExist = false; //如果对象存在,需要判断坐标是否存在,只要该对象存在一组坐标,就认为该对象坐标存在 if (isBoExist) { bo.GeometryList[0].BOID = bo.BOID; isGeometryExist = GeometryeExist(bo.GeometryList[0]); } //坐标且保留 if (isGeometryExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } //覆盖 else { //坐标存在,先删除 if (isGeometryExist) { strSql = new StringBuilder(); strSql.Append(" DELETE FROM GEOMETRY "); strSql.Append(" WHERE BOID =:BOID "); parameters = new OracleParameter[] { new OracleParameter("BOID", OracleDbType.Varchar2, 36) }; parameters[0].Value = bo.BOID; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } foreach (Geometry geometry in bo.GeometryList) { strSql = new StringBuilder(); strSql.Append(" INSERT INTO GEOMETRY( "); strSql.Append(" BOID,NAME,GEOMETRY,SOURCEDB)"); strSql.Append(" VALUES (:BOID,:NAME,MDSYS.SDO_GEOMETRY(:GEOMETRY,4326),:SOURCEDB)"); parameters = new OracleParameter[] { new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("NAME", OracleDbType.Varchar2, 100), new OracleParameter("GEOMETRY", OracleDbType.Clob), new OracleParameter("SOURCEDB", OracleDbType.Varchar2, 100) }; parameters[0].Value = bo.BOID == null?System.Guid.NewGuid().ToString() : bo.BOID; parameters[1].Value = geometry.NAME == null?geometry.GEOMETRY.Split('(')[0] : geometry.NAME; parameters[2].Value = geometry.GEOMETRY; parameters[3].Value = geometry.SOURCEDB; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion #region 添加属性数据 foreach (Property property in bo.PropertyList) { strSql = new StringBuilder(); bool isPropertyExist = false; if (isBoExist) { property.BOID = bo.BOID; isPropertyExist = PropertyExist(property); } //该条属性存在且保留,跳过 if (isPropertyExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } else { if (!isPropertyExist) { strSql.Append(" INSERT INTO PROPERTY( "); strSql.Append(" MD,MDSOURCE,BOID,NS)"); strSql.Append(" VALUES (:MD,:MDSOURCE,:BOID,:NS)"); } else if (isPropertyExist && replaceOrLeave.ToUpper() != "UNCHANGE") { strSql.Append(" UPDATE PROPERTY SET "); strSql.Append(" MD=:MD ,MDSOURCE=:MDSOURCE "); strSql.Append(" WHERE BOID=:BOID AND NS=:NS"); } parameters = new OracleParameter[] { new OracleParameter("MD", OracleDbType.XmlType), new OracleParameter("MDSOURCE", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("NS", OracleDbType.Varchar2, 50) }; parameters[0].Value = property.MD; parameters[1].Value = property.MdSource; parameters[2].Value = bo.BOID; parameters[3].Value = property.NS; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion #region 添加别名 foreach (AliasName aliasName in bo.AliasNameList) { strSql = new StringBuilder(); bool isAliasNameExist = false; if (isBoExist) { aliasName.BOID = bo.BOID; isAliasNameExist = AliasNameExist(aliasName); } //该条属性存在且保留,跳过 if (isAliasNameExist && replaceOrLeave.ToUpper() == "UNCHANGE") { ; } else { strSql = new StringBuilder(); if (!isAliasNameExist) { strSql.Append(" INSERT INTO ALIASNAME( "); strSql.Append(" NAME,BOID,APPDOMAIN)"); strSql.Append(" VALUES (:NAME,:BOID,:APPDOMAIN)"); } else if (isAliasNameExist && replaceOrLeave.ToUpper() != "LEAVE") { strSql.Append(" UPDATE ALIASNAME SET "); strSql.Append(" NAME=:NAME "); strSql.Append(" WHERE BOID=:BOID AND APPDOMAIN=:APPDOMAIN"); } parameters = new OracleParameter[] { new OracleParameter("NAME", OracleDbType.Varchar2, 50), new OracleParameter("BOID", OracleDbType.Varchar2, 36), new OracleParameter("APPDOMAIN", OracleDbType.Varchar2, 50) }; parameters[0].Value = aliasName.NAME; parameters[1].Value = aliasName.BOID; parameters[2].Value = aliasName.APPDOMAIN; sqlEntity = new DBUtility.OracleDBHelper.SQLEntity(); sqlEntity.Sqlstr = strSql.ToString(); sqlEntity.Oracleparameter = parameters; sqlList.Add(sqlEntity); } } #endregion try { //存在需要执行的sql语句 if (sqlList.Count > 0) { i = i + (DBUtility.OracleDBHelper.OracleHelper.ExecuteSql(sqlList) == true ? 1 : 0); if (IsInsert == true) { iInsertCount++; } else { iUpdateCount++; } } } catch (Exception ex) { //记录入库错误信息 subErr.BOName = bo.NAME; subErr.Error = "空间数据有误!"; result.Errors.Add(subErr); //continue; //记录完毕 throw ex; } //构造需要返回的SubmissionResult result.TotalBO = ftList.Count; result.UpdatedBO = iUpdateCount; result.FailedBO = ListsubErr.Count; result.InsertedBO = iInsertCount; //result.Errors.AddRange(ListsubErr); //结束 } return(i); }
/// <summary> /// 根据条件获取3GX数据 /// </summary> /// <param name="filter"></param> /// <returns></returns> public XmlDocument GetFeatures(FeatureFilter filter) { string sqlWhere = string.Empty; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT DISTINCT BO.*,OBJECTTYPE.BOT,OBJECTTYPE.FT FROM BO,OBJECTTYPE"); if (!string.IsNullOrEmpty(filter.BBox)) { strSql.Append(",v_Geometry "); } if (filter.Filter != null) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE BO.BOTID = OBJECTTYPE.BOTID "); strSql.Append(" AND PROPERTY.BOID = BO.BOID "); strSql.Append(string.Format(" AND OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } else { strSql.Append(string.Format(" WHERE OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } //对象名称 if (filter.BOs != null && filter.BOs.Count > 0) { string bos = string.Empty; for (int i = 0; i < filter.BOs.Count; i++) { if (i == filter.BOs.Count - 1) { bos += "'" + filter.BOs[i].Trim() + "'"; } else { bos += "'" + filter.BOs[i].Trim() + "',"; } } strSql.Append(string.Format(" AND BO.NAME in {0} ", bos)); } //空间范围和crs 未启用坐标范围 if (!string.IsNullOrEmpty(filter.BBox)) { strSql.Append(" AND v_Geometry.BOID=BO.BOID "); strSql.Append(string.Format(" AND ( v_Geometry.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", filter.BBox)); strSql.Append(string.Format(" v_Geometry.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", filter.BBox)); } //属性 if (filter.Filter != null) { JObject jObj = JObject.Parse(filter.Filter.ToString()); string bot = SqlServerDBHelper.ExecuteQueryText <string>(string.Format("SELECT BOT FROM OBJECTTYPE WHERE FT='{0}'", filter.FT)).FirstOrDefault(); strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(jObj.ToString(), bot).ToString() + " )"); } DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); List <GeoFeature> ftList = new List <GeoFeature>(); foreach (DataRow row in dt.Rows) { GeoFeature ft = new GeoFeature(); ft.BOID = row["Boid"].ToString(); ft.BOT = row["BOT"].ToString(); ft.FT = row["FT"].ToString(); ft.NAME = row["Name"].ToString(); ft.AliasNameList = Comm.GetAliasNameByBoid(ft.BOID); ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); ftList.Add(ft); } return(GGGXParse.ConvertFT.FeatureToGGGX(ftList)); }
/// <summary> /// 根据查询条件获取GGGX数据 /// </summary> /// <param name="bot"></param> /// <param name="bos"></param> /// <param name="filter"></param> /// <param name="category"></param> /// <returns></returns> public XmlDocument Get3GXByFilter(string bot, List <string> bos, string filter, GGGXDataCategory category) { string sqlWhere = string.Empty; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT DISTINCT BO.*,OBJECTTYPE.BOT,OBJECTTYPE.FT FROM BO,OBJECTTYPE"); if (!string.IsNullOrEmpty(filter)) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE BO.BOTID = OBJECTTYPE.BOTID "); strSql.Append(" AND PROPERTY.BOID = BO.BOID "); strSql.Append(string.Format(" AND OBJECTTYPE.BOT='{0}' ", bot)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } else { strSql.Append(string.Format(" WHERE OBJECTTYPE.BOT='{0}' ", bot)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID "); } //对象名称 if (bos != null && bos.Count > 0) { string strBos = string.Empty; for (int i = 0; i < bos.Count; i++) { if (i == bos.Count - 1) { strBos += "'" + bos[i].Trim() + "'"; } else { strBos += "'" + bos[i].Trim() + "',"; } } strSql.Append(string.Format(" AND BO.NAME in ({0}) ", strBos)); } //属性 if (!string.IsNullOrEmpty(filter)) { strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(filter, bot).ToString() + " )"); } DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); List <GeoFeature> ftList = new List <GeoFeature>(); foreach (DataRow row in dt.Rows) { GeoFeature ft = new GeoFeature(); ft.BOID = row["Boid"].ToString(); ft.BOT = row["BOT"].ToString(); ft.FT = row["FT"].ToString(); ft.NAME = row["Name"].ToString(); if (category == GGGXDataCategory.B) { ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); } else if (category == GGGXDataCategory.P) { ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = null; } else if (category == GGGXDataCategory.G) { ft.PropertyList = null; ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); } ftList.Add(ft); } return(GGGXParse.ConvertFT.FeatureToGGGX(ftList)); }
/// <summary> /// 根据条件获取3GX数据 /// </summary> /// <param name="filter"></param> /// <returns></returns> public XmlDocument GetFeatures(FeatureFilter filter) { string sqlWhere = string.Empty; StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT DISTINCT BO.*,OBJECTTYPE.BOT,OBJECTTYPE.FT FROM BO,OBJECTTYPE,Geometry"); if (filter.Filter != null) { strSql.Append(" ,PROPERTY PROPERTY "); strSql.Append(" WHERE BO.BOTID = OBJECTTYPE.BOTID "); strSql.Append(" AND PROPERTY.BOID = BO.BOID "); strSql.Append(" AND GEOMETRY.BOID=BO.BOID "); strSql.Append(string.Format(" AND OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID AND BO.BOID=GEOMETRY.BOID "); } else { strSql.Append(string.Format(" WHERE OBJECTTYPE.FT='{0}' ", filter.FT)); strSql.Append(" AND BO.BOTID=OBJECTTYPE.BOTID AND BO.BOID=GEOMETRY.BOID "); } //对象名称 if (filter.BOs != null && filter.BOs.Count > 0) { string bos = string.Empty; for (int i = 0; i < filter.BOs.Count; i++) { if (i == filter.BOs.Count - 1) { bos += "'" + filter.BOs[i].Trim() + "'"; } else { bos += "'" + filter.BOs[i].Trim() + "',"; } } strSql.Append(string.Format(" AND BO.NAME in {0} ", bos)); } //空间范围和crs 未启用坐标范围 if (!string.IsNullOrEmpty(filter.BBox)) { strSql.Append(" AND SDO_FILTER(GEOMETRY.GEOMETRY, "); strSql.Append(" SDO_GEOMETRY('" + filter.BBox + "',4326), "); strSql.Append(" 'QUERYTYPE=WINDOW') = 'TRUE' "); } //属性 if (filter.Filter != null) { JObject jObj = JObject.Parse(filter.Filter.ToString()); string bot = OracleDBHelper.OracleHelper.ExecuteQueryText <string>(string.Format("SELECT BOT FROM OBJECTTYPE WHERE FT='{0}'", filter.FT)).FirstOrDefault(); strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(jObj.ToString(), bot).ToString() + " )"); } DataTable dt = OracleDBHelper.OracleHelper.ExecuteQueryText(strSql.ToString()).Tables[0]; List <GeoFeature> ftList = new List <GeoFeature>(); foreach (DataRow row in dt.Rows) { GeoFeature ft = new GeoFeature(); ft.BOID = row["Boid"].ToString(); ft.BOT = row["BOT"].ToString(); ft.FT = row["FT"].ToString(); ft.NAME = row["Name"].ToString(); ft.CLASS = GF.Server.Oracle.Comm.GetClassByBoid(ft.BOID); ft.AliasNameList = Comm.GetAliasNameByBoid(ft.BOID); ft.PropertyList = Comm.GetPropertyByBoid(ft.BOID); ft.GeometryList = Comm.GetGeometryByBoid(ft.BOID); ftList.Add(ft); } return(GGGXParse.ConvertFT.FeatureToGGGX(ftList)); }