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