/// <summary>
/// 根据业务对象类型和过滤条件获取对象列表
/// </summary>
/// <param name="bot"></param>
/// <param name="filter"></param>
/// <returns></returns>
public BOCollection GetBOListByType(string bot, string filter)
{
BOCollection list = new BOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT FROM ");
strSql.Append(" BO T,");
strSql.Append(" OBJECTTYPE T1 ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
}
strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot));
strSql.Append(" AND T.BOTID = T1.BOTID ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" AND PROPERTY.BOID = T.BOID ");
}
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")");
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString()));
return(list);
}
/// <summary>
/// 根据应用场景和过滤条件查询业务对象。通过对象的参数集进行过滤，返回符合条件的对象列表
/// </summary>
/// <param name="bot">业务对象类型</param>
/// <param name="wktBBox">空间范围</param>
/// <param name="filte">过滤条件</param>
/// <returns></returns>
public BOCollection GetBOListByFilter(string bot, string wktBBox, string filte)
{
//sdo_point_type（坐下坐标，右上坐标）。
BOCollection list = new BOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT T.BOID, T.NAME, T1.BOT ");
strSql.Append(" FROM ");
if (!string.IsNullOrEmpty(wktBBox))
{
strSql.Append(" V_GEOMETRY A,");
}
strSql.Append(" BO T,");
strSql.Append(" OBJECTTYPE T1 ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" ,PROPERTY PROPERTY ");
}
strSql.Append(string.Format(" WHERE T1.BOT = '{0}' ", bot));
if (!string.IsNullOrEmpty(wktBBox))
{
//strSql.Append(" GEOMETRY A,");
strSql.Append(" AND A.BOID = T.BOID ");
}
strSql.Append(" AND T.BOTID = T1.BOTID ");
//属性过滤条件不为空
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" AND PROPERTY.BOID = T.BOID ");
}
//空间范围不为空
if (!string.IsNullOrEmpty(wktBBox))
{
strSql.Append(string.Format(" AND ( A.GEOMETRY.STWithin(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 or ", wktBBox));
strSql.Append(string.Format(" A.GEOMETRY.STIntersects(GEOGRAPHY::STGeomFromText('{0}', 4326))=1 ) ", wktBBox));
}
if (!string.IsNullOrEmpty(filte))
{
strSql.Append(" AND " + "( " + MongoJsonToSql.JsonToSql(filte, bot).ToString() + " )");
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <BO>(strSql.ToString()));
return(list);
}
public NearBOCollection GetNearBOByBo(decimal distince, string bot, string filter, string strSqlGetGeo)
{
NearBOCollection list = new NearBOCollection();
StringBuilder strSql = new StringBuilder();
strSql.Append(" SELECT DISTINCT B.BOID, T.NAME, O.BOT, SUBSTRING(B.SPACELOCATION,0,3800) AS SPACELOCATION, B.DIST DISTINCE ");
strSql.Append(" FROM (SELECT A.BOID, A.NAME, A.GEOMETRY.STAsText() AS SPACELOCATION, ");
strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY ) AS DIST ", strSqlGetGeo));
strSql.Append(" FROM V_GEOMETRY A ");
//过滤条件
if (!string.IsNullOrEmpty(filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = A.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(filter, bot).ToString() + ")");
strSql.Append(" AND ");
}
else
{
strSql.Append(" WHERE ");
}
strSql.Append(string.Format(" ({0}).STDistance(A.GEOMETRY )<{1} ", strSqlGetGeo, distince));
strSql.Append(" ) B ");
strSql.Append(" LEFT JOIN BO T ");
strSql.Append(" ON B.BOID = T.BOID ");
strSql.Append(" LEFT JOIN OBJECTTYPE O ");
strSql.Append(" ON O.BOTID = T.BOTID ");
//对象类型
if (!string.IsNullOrEmpty(bot))
{
strSql.Append(string.Format(" WHERE O.BOT = '{0}'", bot));
}
list.AddRange(SqlServerDBHelper.ExecuteQueryText <NearBO>(strSql.ToString()));
return(list);
}
/// <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="model"></param>
/// <returns></returns>
private List <TreeBO> GetTreeBONode(BOTreeNodeTemplate nodeTemplate, List <TreeBO> treeBOList, BORelation relation)
{
foreach (var bo in treeBOList)
{
StringBuilder strSql = new StringBuilder();
if (string.IsNullOrEmpty(relation.ToString()) || relation == BORelation.Space)
{
strSql.Append(" SELECT T.BOID, T.NAME, T1.BOT,");
strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID ");
strSql.Append(" FROM BO T, OBJECTTYPE T1, GEOMETRY T3 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(nodeTemplate.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T.BOID ");
JObject jObj = JObject.Parse(nodeTemplate.Filter);
JToken strFilter = jObj["filter"];
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(strFilter.ToString(), bo.BOT).ToString() + ")");
strSql.Append(" AND T.BOTID = T1.BOTID ");
strSql.Append(" AND T.BOID = T3.BOID ");
}
else
{
strSql.Append(" WHERE T.BOTID = T1.BOTID ");
strSql.Append(" AND T.BOID = T3.BOID ");
}
#endregion
//空间包含的子对象
strSql.Append(" AND SDO_FILTER(t3.GEOMETRY, ");
strSql.Append(" (SELECT T2.GEOMETRY ");
strSql.Append(" FROM GEOMETRY T2 ");
strSql.Append(string.Format(" WHERE T2.BOID ='{0}'), ", bo.BOID));
strSql.Append(" 'QUERYTYPE=WINDOW') = 'TRUE' ");
}
else
{
strSql.Append(" SELECT T5.BOID, T5.NAME, T6.BOT, ");
strSql.Append("'" + bo.BOID + "'" + " AS PARENTBOID ");
strSql.Append(" FROM RELATION T4, BO T5, OBJECTTYPE T6 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(nodeTemplate.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T5.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(nodeTemplate.Filter, bo.BOT).ToString() + ")");
strSql.Append(" AND T4.RTID = (SELECT T.RTID ");
}
else
{
strSql.Append(" WHERE T4.RTID = (SELECT T.RTID ");
}
#endregion
strSql.Append(" FROM RELTYPE T ");
strSql.Append(" JOIN OBJECTTYPE T1 ");
strSql.Append(" ON T.BOTID1 = T1.BOTID ");
strSql.Append(" JOIN OBJECTTYPE T2 ");
strSql.Append(" ON T.BOTID2 = T2.BOTID ");
strSql.Append(string.Format(" AND T1.BOT = '{0}' ", bo.BOT));
strSql.Append(string.Format(" AND T2.BOT = '{0}') ", nodeTemplate.BOT));
strSql.Append(" AND T4.BOID2 = T5.BOID ");
strSql.Append(string.Format(" AND T4.BOID1 = '{0}' ", bo.BOID));
strSql.Append(" AND T5.BOTID = T6.BOTID ");
}
//过滤条件未添加
if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0)
{
if (nodeTemplate.BOs != null && nodeTemplate.BOs.Count() > 0)
{
string strBos = string.Empty;
for (int i = 0; i < nodeTemplate.BOs.Count(); i++)
{
if (i == nodeTemplate.BOs.Count() - 1)
{
strBos += "'" + nodeTemplate.BOs[i].Trim() + "'";
}
else
{
strBos += "'" + nodeTemplate.BOs[i].Trim() + "',";
}
}
strSql.Append(string.Format(" AND T5.NAME IN ({0})", strBos));
strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT));
}
}
else
{
strSql.Append(string.Format(" AND T6.BOT = '{0}' ", nodeTemplate.BOT));
}
List <TreeBO> boList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString());
listTemp.AddRange(boList);
if (nodeTemplate.Children != null)
{
foreach (var children in nodeTemplate.Children)
{
GetTreeBONode(children, boList, nodeTemplate.Relation);
}
}
}
return(listTemp);
}
/// <summary>
/// 根据业务对象ID获取指定BO的父节点、下级节点、兄弟节点、相邻节点（父节点、兄弟节点和下级节点）和子树。返回的节点中不包括自己
/// </summary>
/// <param name="template">树模板</param>
/// <returns></returns>
public TreeBOCollection GetBOTree(BOTreeTemplate template)
{
TreeBOCollection list = new TreeBOCollection();
foreach (var item in template)
{
string strBos = string.Empty;
TreeBO treeBo = new TreeBO();
if (item.BOs != null && item.BOs.Count() > 0)
{
for (int i = 0; i < item.BOs.Count(); i++)
{
if (i == item.BOs.Count() - 1)
{
strBos += "'" + item.BOs[i].Trim() + "'";
}
else
{
strBos += "'" + item.BOs[i].Trim() + "',";
}
}
}
StringBuilder strSql = new StringBuilder();
SqlParameter[] parameters;
strSql.Append(" SELECT T.BOID, T1.BOT, T.NAME, '' AS ParentBOID ");
strSql.Append(" FROM BO T, OBJECTTYPE T1 ");
//过滤条件
#region
if (!string.IsNullOrEmpty(item.Filter))
{
strSql.Append(" ,PROPERTY PROPERTY ");
strSql.Append(" WHERE PROPERTY.BOID = T.BOID ");
strSql.Append(" AND " + "(" + MongoJsonToSql.JsonToSql(item.Filter, item.BOT).ToString() + ")");
strSql.Append(" AND T.BOTID = T1.BOTID ");
}
else
{
strSql.Append(" WHERE T.BOTID = T1.BOTID ");
}
#endregion
if (item.BOs != null && item.BOs.Count() > 0)
{
strSql.Append(string.Format(" AND T.NAME IN({0}) ", strBos));
strSql.Append(" AND T1.BOT =@BOT ");
}
else
{
strSql.Append(" AND T1.BOT =@BOT ");
}
parameters = new SqlParameter[] {
new SqlParameter("BOT", SqlDbType.VarChar, 100)
};
parameters[0].Value = item.BOT;
List <TreeBO> treeBOList = new List <TreeBO>();
//父节点
treeBOList = SqlServerDBHelper.ExecuteQueryText <TreeBO>(strSql.ToString(), parameters);
list.AddRange(treeBOList);
if (item.Children != null)
{
foreach (BOTreeNodeTemplate nodeTemplate in item.Children)
{
list.AddRange(GetTreeBONode(nodeTemplate, treeBOList, item.Relation));
}
}
}
return(list);
}
/// <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));
}
