/// <summary> /// 获取应用场景已经参数 /// </summary> /// <returns></returns> public AppDomainCollection GetAppDomains() { AppDomainCollection coll = new AppDomainCollection(); List <Jurassic.PKS.Service.GF.AppDomain> list = new List <Jurassic.PKS.Service.GF.AppDomain>(); StringBuilder strSql = new StringBuilder(); strSql.Append(" WITH TAB AS( "); strSql.Append(" SELECT T.BOT, T1.NS "); strSql.Append(" FROM OBJECTTYPE T "); strSql.Append(" LEFT JOIN OBJTYPEPROPERTY T1 "); strSql.Append(" ON T.BOTID = T1.BOTID) "); strSql.Append(" select DISTINCT bot,nss=STUFF((SELECT ','+ ns FROM TAB t WHERE bot=t1.bot FOR XML PATH('')), 1, 1, '') "); strSql.Append(" from tab t1 order by bot "); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); foreach (DataRow row in dt.Rows) { Jurassic.PKS.Service.GF.AppDomain appdomain = new Jurassic.PKS.Service.GF.AppDomain(); appdomain.BOT = row["BOT"].ToString(); if (!string.IsNullOrEmpty(row["NSS"].ToString())) { appdomain.Appdomain = row["NSS"].ToString().Split(',').ToList(); } list.Add(appdomain); } coll.AddRange(list); return(coll); }
/// <summary> /// 根据ID获取对象空间坐标 /// </summary> /// <param name="boid"></param> /// <returns></returns> public List <GeometryModel> GetGeometryByID(string boid) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT BOID,GATHERID,NAME,T.GEOMETRY.MakeValid().STAsText() AS GEOMETRY,SOURCEDB FROM GEOMETRY T "); strSql.Append(" WHERE BOID =@BOID"); SqlParameter[] parameters = { new SqlParameter("BOID", SqlDbType.VarChar, 36) }; parameters[0].Value = boid; DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters); List <GeometryModel> list = new List <GeometryModel>(); if (dt != null && dt.Rows.Count > 0) { foreach (DataRow item in dt.Rows) { GeometryModel model = new GeometryModel(); model.BOID = item["BOID"].ToString(); model.GATHERID = item["GATHERID"].ToString(); model.GEOMETRY = item["GEOMETRY"].ToString(); model.NAME = item["NAME"].ToString(); model.SOURCEDB = item["SOURCEDB"].ToString(); list.Add(model); } } return(list); }
/// <summary> /// 获取BO的叙词分类。主要用于短语分词的时候识别业务对象是什么类型 /// </summary> /// <returns></returns> public TermBOCollection GetCCTermOfBO() { TermBOCollection termBolist = new TermBOCollection(); StringBuilder strSql = new StringBuilder(); strSql.Append(" WITH TAB AS( "); strSql.Append(" SELECT T.BOID,T.NAME,T2.BOT,T1.NAME NAME1 FROM BO T "); strSql.Append(" LEFT JOIN ALIASNAME T1 "); strSql.Append(" ON T.BOID=T1.BOID "); strSql.Append(" LEFT JOIN OBJECTTYPE T2 "); strSql.Append(" ON T.BOTID=T2.BOTID )"); strSql.Append(" SELECT BOID,NAME,BOT, "); strSql.Append(" ALIAS= STUFF((SELECT ','+[NAME1] FROM TAB t WHERE BOID=t1.BOID and BOT=t1.BOT and NAME=t1.NAME FOR XML PATH('')), 1, 1, '') "); strSql.Append(" FROM TAB T1 "); strSql.Append(" GROUP BY BOID, NAME,BOT "); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); foreach (DataRow row in dt.Rows) { TermBO termBo = new TermBO(); termBo.BOID = row["BOID"].ToString(); termBo.BOT = row["BOT"].ToString(); termBo.Name = row["NAME"].ToString(); if (!string.IsNullOrEmpty(row["ALIAS"].ToString())) { termBo.Alias = row["ALIAS"].ToString().Split(',').ToList(); } termBolist.Add(termBo); } return(termBolist); }
/// <summary> /// 获取FT集合 /// </summary> /// <returns></returns> public FTCCollection GetFTCList() { FTCCollection list = new FTCCollection(); FTC ftc = new FTC("平面"); ftc.Name = "平面"; //获取全部对象类型 DataTable objTypeDt = SqlServerDBHelper.GetDataTable("SELECT BOTID,FT FROM OBJECTTYPE "); //DataTable dt; foreach (DataRow row in objTypeDt.Rows) { FT ft = new FT(row["FT"].ToString()); ft.Name = row["FT"].ToString(); SqlParameter[] parameters = { new SqlParameter("BOTID", SqlDbType.VarChar, 36) }; parameters[0].Value = row["BOTID"].ToString(); DataTable dt = new DataTable(); //MD不截取发布服务出错 dt = SqlServerDBHelper.GetDataTable("SELECT MD AS MD,NS FROM OBJTYPEPROPERTY WHERE BOTID = @BOTID ", parameters); for (int i = 0; i < dt.Rows.Count; i++) { if (!string.IsNullOrEmpty(dt.Rows[i]["MD"].ToString())) { NS ns = new NS(dt.Rows[i]["NS"].ToString()); ns.Name = dt.Rows[i]["NS"].ToString(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.LoadXml(dt.Rows[i]["MD"].ToString()); for (int j = 0; j < xmlDoc.SelectNodes("PropertySet/P").Count; j++) { FeatureParameter para = new FeatureParameter(xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[0].Value); para.Name = xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[0].Value; if (xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[1].Value.ToUpper() == Jurassic.PKS.Service.PropertyDataType.String.ToString().ToUpper()) { para.DataType = PropertyDataType.String; } else if (xmlDoc.SelectNodes("PropertySet/P").Item(j).Attributes[1].Value.ToUpper() == Jurassic.PKS.Service.PropertyDataType.Decimal.ToString().ToUpper()) { para.DataType = PropertyDataType.Decimal; } else { para.DataType = PropertyDataType.Date; } ns.Parameters.Add(para); } ft.NSs.Add(ns); } } ftc.FTs.Add(ft); } list.Add(ftc); return(list); }
/// <summary> /// 空间坐标是否存在 /// </summary> /// <param name="Geometry"></param> /// <returns></returns> public bool ExistGeometry(GeometryModel Geometry) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT BOID,NAME,T.GEOMETRY.MakeValid().STAsText() AS GEOMETRY,SOURCEDB FROM GEOMETRY T "); strSql.Append(" WHERE BOID =@BOID"); SqlParameter[] parameters = { new SqlParameter("BOID", SqlDbType.VarChar, 36) }; parameters[0].Value = Geometry.BOID; return(SqlServerDBHelper.GetDataTable(strSql.ToString(), parameters).Rows.Count > 0 ? true : false); }
/// <summary> /// 获取指定查询条件的取值 /// </summary> /// <param name="ft"></param> /// <param name="parameter"></param> /// <returns></returns> public List <string> GetDomain(string ft, string parameter) { string[] strArr = parameter.Split('.').ToArray(); StringBuilder strSql = new StringBuilder(); List <string> list = new List <string>(); strSql.Append(" SELECT VS=COL.value('text()[1]','VARCHAR(MAX)') "); strSql.Append(" FROM OBJECTTYPE T2,OBJTYPEPROPERTY X CROSS APPLY MD.nodes('/PropertySet/P') AS TBL(COL) "); strSql.Append(string.Format(" WHERE X.BOTID=T2.BOTID AND X.NS='{0}' AND T2.FT='{1}' AND COL.value('@n','VARCHAR(MAX)')='{2}' ", strArr[0], ft, strArr[1])); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); if (dt.Rows.Count > 0) { list = dt.Rows[0]["VS"].ToString().Split(',').ToList(); } return(list); }
/// <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)); }
/// <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> /// 根据业务对象类型和应用域获取属性定义信息 /// </summary> /// <param name="bot"></param> /// <param name="appDomain"></param> /// <param name="names"></param> /// <returns></returns> public List <PropertySchema> GetPropertySchema(string bot, string appDomain, List <PropertyName> names) { List <PropertySchema> list = new List <PropertySchema>(); StringBuilder strSql = new StringBuilder(); List <DataTable> DtL = new List <DataTable>(); if (names.Count == 0) { #region 获取应用场景下的所有的参数以及参数的值域 strSql.Append(" SELECT Name = col.value('@n', 'varchar(max)'), "); strSql.Append(" datatype = col.value('@t', 'varchar(max)'), "); strSql.Append(" vs = col.value('text()[1]', 'varchar(max)') "); strSql.Append(" from OBJTYPEPROPERTY X CROSS APPLY md.nodes('/PropertySet/P') AS Tbl(col) "); strSql.Append(" ,OBJECTTYPE T2 "); strSql.Append(string.Format(" WHERE X.BOTID = T2.BOTID AND t2.BOT= '{0}' AND X.NS = '{1}' ", bot, appDomain)); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); DtL.Add(dt); #endregion } else { foreach (var item in names) { strSql = new StringBuilder(); if (item.ValueType == ValueTypes.All) { #region 可能值+实际值 strSql = new StringBuilder(); strSql.Append(" WITH TAB AS "); strSql.Append(" (SELECT DISTINCT "); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@t)[1]','varchar(max)') as datatype , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@n)[1]','varchar(max)') as name , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"][text()])[1]','varchar(max)') as vs ", item.Name)); strSql.Append(" FROM property X, objecttype t,bo "); strSql.Append(string.Format(" WHERE bo.boid=x.boid and bo.BOTID=t.BOTID AND t.BOT= '{0}' AND X.NS = '{1}' ", bot, appDomain)); strSql.Append(" union "); strSql.Append(" SELECT DISTINCT "); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@t)[1]','varchar(max)') as datatype , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@n)[1]','varchar(max)') as name , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"][text()])[1]','varchar(max)') as vs ", item.Name)); strSql.Append(" FROM OBJTYPEPROPERTY X,OBJECTTYPE T2 "); strSql.Append(string.Format(" WHERE X.BOTID = T2.BOTID AND t2.BOT= '{0}' AND X.NS = '{1}' ", bot, appDomain)); strSql.Append(" ) SELECT datatype,name, "); strSql.Append(" vs= STUFF((SELECT ','+vs FROM TAB t WHERE datatype=t1.datatype and name=t1.name FOR XML PATH('')), 1, 1, '') "); strSql.Append(" FROM TAB T1 "); strSql.Append(" GROUP BY datatype, name "); DataTable factDt = SqlServerDBHelper.GetDataTable(strSql.ToString()); DtL.Add(factDt); #endregion } else if (item.ValueType == ValueTypes.Enum)//取枚举值 { #region 获取当前应用场景下参数以及参数的枚举值 strSql.Append(" SELECT DISTINCT "); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@t)[1]','varchar(max)') as datatype , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@n)[1]','varchar(max)') as name , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"][text()])[1]','varchar(max)') as vs ", item.Name)); strSql.Append(" FROM OBJTYPEPROPERTY X,OBJECTTYPE T2 "); strSql.Append(string.Format(" WHERE X.BOTID = T2.BOTID AND t2.BOT= '{0}' AND X.NS = '{1}' ", bot, appDomain)); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); DtL.Add(dt); #endregion } else if (item.ValueType == ValueTypes.Fact)//取实际值 { #region 取实际值 strSql.Append(" WITH TAB AS "); strSql.Append(" (SELECT DISTINCT "); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@t)[1]','varchar(max)') as datatype , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@n)[1]','varchar(max)') as dataname , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"][text()])[1]','varchar(max)') as datavalue ", item.Name)); strSql.Append(" FROM property X, objecttype t,bo "); strSql.Append(string.Format(" WHERE bo.boid=x.boid and bo.BOTID=t.BOTID AND t.BOT= '{0}' AND X.NS = '{1}') ", bot, appDomain)); strSql.Append(" SELECT datatype,dataname as name, "); strSql.Append(" vs= STUFF((SELECT ','+datavalue FROM TAB t WHERE datatype=t1.datatype and dataname=t1.dataname FOR XML PATH('')), 1, 1, '') "); strSql.Append(" FROM TAB T1 "); strSql.Append(" GROUP BY datatype, dataname "); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); DtL.Add(dt); #endregion } else { #region 获取参数的数据类型 strSql.Append(" SELECT DISTINCT "); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@t)[1]','varchar(max)') as datatype , ", item.Name)); strSql.Append(string.Format(" x.MD.value('(/PropertySet/P[@n=\"{0}\"]/@n)[1]','varchar(max)') as name ", item.Name)); strSql.Append(" FROM OBJTYPEPROPERTY X,OBJECTTYPE T2 "); strSql.Append(string.Format(" WHERE X.BOTID = T2.BOTID AND t2.BOT= '{0}' AND X.NS = '{1}' ", bot, appDomain)); DataTable dt = SqlServerDBHelper.GetDataTable(strSql.ToString()); DtL.Add(dt); #endregion } } } foreach (DataTable dt in DtL) { foreach (DataRow row in dt.Rows) { PropertySchema proSchema = new PropertySchema(); proSchema.Name = row["NAME"].ToString(); if (PropertyDataType.Date.ToString().ToUpper() == row["DataType"].ToString().ToUpper()) { proSchema.DataType = PropertyDataType.Date; } else if (PropertyDataType.Decimal.ToString().ToUpper() == row["DataType"].ToString().ToUpper()) { proSchema.DataType = PropertyDataType.Decimal; } else { proSchema.DataType = PropertyDataType.String; } proSchema.Values = row.Table.Columns.Contains("VS") == false?null: row["VS"].ToString().Split(',').ToList(); proSchema.Values = proSchema.Values == null?null: proSchema.Values.Distinct <string>().ToList(); list.Add(proSchema); } } return(list); }